mssql sqlserver 查询当前数据库下所有表的占用空间的方法分享


摘要:
下文将分享sql脚本查询数据库下所有表空间的信息,如下所示:
实验环境:sql server 2008 R2


select 
name '数据表名称', row_Count as '表行数',
(reservedpages * 8) '表空间已用(K)',
(pages * 8)  '表数据占用空间(K)',
(CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '表所涉及索引占用空间(K)',
(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '表未用空间(K)',
LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + ' G') as '表已用空间(G)'
from(
SELECT name,
SUM (reserved_page_count) as reservedpages ,
SUM (used_page_count) as usedpages ,
SUM (
     CASE
        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
        ELSE lob_used_page_count + row_overflow_used_page_count
     END
    ) as pages,
SUM (
   CASE
    WHEN (index_id < 2) THEN row_count
    ELSE 0
    END
   )  as row_Count
FROM sys.dm_db_partition_stats
inner join sys.objects on sys.dm_db_partition_stats.object_id=sys.objects.object_id
where type='U'
group by sys.objects.name ) t
order by '表空间已用(K)' desc