SqlServer2005 查看表占用磁盘空间情况

以下Sql语句来源于SqlServer系统存储过程sp_spaceused，经过重写将单表查询改为了所有用户表查询，经过验证查询结果与 sp_spaceused结果一致。

SELECT

name '表名',
convert (char(11), row_Count) as '数据条数',
(reservedpages * 8) '已用空间(KB)',
(pages * 8) '数据占用空间(KB)',
(CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '索引占用空间(KB)',
(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '未用空间(KB)',
LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + ' GB') as '已用空间(GB)'
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

union

SELECT sys.objects.name,
sum(reserved_page_count) as reservedpages,
sum(used_page_count) as usedpages,
0 as pages,
0 as row_count
from sys.objects inner join sys.internal_tables on
 sys.objects.object_id = sys.internal_tables.parent_id
inner join sys.dm_db_partition_stats on sys.dm_db_partition_stats.object_id=sys.internal_tables.object_id
where sys.internal_tables.internal_type IN (202,204,211,212,213,214,215,216)
group by sys.objects.name) t
order by '已用空间(KB)' desc