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

파티션을 고려하지 못한 부분과 index_id 비교를 잘못해서 인덱스 크기 잘못 계산된 부분 재공유 합니다.
파티션을 이루는 파티션의 개수와 압축을 이용한 파티션의 개수를 같이 보여 줍니다.
SQL Server 2008 Enterprise Edition이 아니면 압축 관련 부분은 주석 처리하시면 됩니다.

select    object_name(max(object_id))

,        max(b.PartitionCnt) as 'PartitionCnt'

,        max(b.CompressionCnt) as 'CompressionPartitionCnt'

,        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    (    select    object_id

            ,        index_id

            ,        sum(used_page_count) as used_page_count

            ,        sum(reserved_page_count) as reserved_page_count

            ,        sum(row_count) as row_count

            ,        sum(in_row_used_page_count) as in_row_used_page_count

            ,        sum(in_row_reserved_page_count) as in_row_reserved_page_count

            ,        sum(lob_used_page_count) as lob_used_page_count

            ,        sum(lob_reserved_page_count) as lob_reserved_page_count

            ,        sum(row_overflow_used_page_count) as row_overflow_used_page_count

            ,        sum(row_overflow_reserved_page_count) as row_overflow_reserved_page_count

            from sys.dm_db_partition_stats with(nolock)

            where object_name(object_id) not like 'sys%'

            group by object_id, index_id

        ) as a

        cross apply (    select    count(aa.object_id) as 'PartitionCnt'

                        ,        sum(case when aa.data_compression > 0 then 1 else 0 end) as 'CompressionCnt'

                        from sys.partitions aa

                        where aa.object_id = a.object_id and aa.index_id = a.index_id

                        group by aa.object_id, aa.index_id

                    ) b

group by object_id

order by 'Total Used(KB)' desc