[MSSQL-DMV] 각 테이블에 할당된 공간과 실제 데이터와 인덱스가 차지하고 있는 공간 확인

 

set nocount on;

 

select

         object_name(max(object_id)) [Object Name]

       , sum(used_page_count) * 8 as 'Total Used(KB)'

       , sum(reserved_page_count) * 8 as 'Total Reserved(KB)'

       , sum(case when index_id = 0 then used_page_count else 0 end) * 8 as 'Heap(KB)'

       , sum(case when index_id = 1 then used_page_count else 0 end) * 8 as 'Cluster(KB)'

       , sum(case when index_id < 2 then used_page_count else 0 end) * 8 as 'Index(KB)'

       , max(row_count) as 'RowCount'

       , sum(in_row_used_page_count) * 8 as 'Row Used(KB)'

       , sum(in_row_reserved_page_count) * 8 as 'Row Reserved(KB)'

       , sum(lob_used_page_count) * 8 as 'LOB Used(KB)'

       , sum(lob_reserved_page_count) * 8 as 'LOB Reserved(KB)'

       , sum(row_overflow_used_page_count) * 8 as 'Overflow Used(KB)'

       , sum(row_overflow_reserved_page_count) * 8 as 'Overflow Reserved(KB)'

from sys.dm_db_partition_stats with(nolock)

where object_name(object_id) not like 'sys%'

group by object_id

order by 2 desc