1 스키마별 디스크 사용량

2 테이블별 디스크 사용량

--열라 귀찮다..
if object_id('tempdb..#size') is not null
    drop table #size

create table #size
(
	name varchar(500)
,	rows bigint
,	reserved varchar(500)
,	data varchar(500)
,	index_size varchar(500)
,	unused varchar(500)
);

if object_id('tempdb..#temp') is not null
    drop table #temp

create table #temp
(
	[schema_name] varchar(500)
,	table_name varchar(500)
,	rows bigint
,	reserved varchar(500)
,	data varchar(500)
,	index_size varchar(500)
,	unused varchar(500)
);

declare 
	@sch_name varchar(50)
,	@tbl_name varchar(50)
,	@nm varchar(100);

declare cur cursor for
	select table_schema, table_name 
	from information_schema.tables

open cur;
fetch next from cur into @sch_name, @tbl_name;
while @@fetch_status not in (-1, -2)
begin
	set @nm = @sch_name + '.' + @tbl_name;

	insert #size exec sp_spaceused @nm;

	insert #temp
	select @sch_name, * from #size;

	truncate table #size;

	fetch next from cur into @sch_name, @tbl_name;
end

CLOSE cur;
DEALLOCATE cur;

select 
	[schema_name]
,	sum(reserved) - sum(unused) disk_size
from (
	select 
		[schema_name]
	,	convert(real, replace(reserved, ' KB', '')) / 1024 reserved
	,	convert(real, replace(unused, ' KB', '')) / 1024 unused
	from #temp
) t
group by [schema_name]
order by disk_size desc

select 
	[schema_name]
,	table_name
,	rows
,	data 
,	index_size
,	convert(real, replace(reserved, ' KB', '')) / 1024 reserved
,	convert(real, replace(unused, ' KB', '')) / 1024 unused
from #temp
where rows is not null
order by reserved desc

drop table #temp
drop table #size

if object_id('tempdb..#temp') is not null
    drop table #temp

create table #temp
(
    name varchar(255)
,   rows bigint
,   reserved varchar(50)
,   data varchar(50)
,   index_size varchar(50)
,   unused varchar(50)
)

if object_id('tempdb..#temp2') is not null
    drop table #temp2

create table #temp2
(
    name varchar(255)
,   rows bigint
,   reserved varchar(50)
,   data varchar(50)
,   index_size varchar(50)
,   unused varchar(50)
)

declare 
    @tname varchar(255); 

declare cur cursor for
select table_schema + '.' + table_name
from information_schema.tables

open cur;
fetch next from cur into @tname;
while @@FETCH_STATUS not in (-1, -2)
begin

    insert #temp2 exec sp_spaceused @tname

    update #temp2
    set name = @tname

    insert #temp 
    select * from #temp2
    truncate table #temp2

	fetch next from cur into @tname;
end

close cur;
deallocate cur;

select 
    name
,   rows
,   replace(reserved, 'KB', '') / 1024 reserved
,   replace(data, 'KB', '') / 1024 data
,   replace(index_size, 'KB', '') / 1024 index_size
,   replace(reserved, 'KB', '') / 1024 unused
from #temp
order by replace(reserved, 'KB', '') / 1024  desc