查询sqlserver数据库中，数据占的空间和索引占的空间

SELECT '所有表' AS [表名],'总计' AS [架构名],CAST(SUM(TotalSpaceKB) / 1048576.0 AS DECIMAL(18,2)) AS [总空间(GB)], CAST(SUM(UsedSpaceKB) / 1048576.0 AS DECIMAL(18,2)) AS [已用空间(GB)],CAST(SUM(UnusedSpaceKB) / 1048576.0 AS DECIMAL(18,2)) AS [未用空间(GB)],SUM(RowCounts) AS [行数],CAST(SUM(DataSizeKB) / 1048576.0 AS DECIMAL(18,2)) AS [数据大小(GB)],  -- 修正命名CAST(SUM(IndexSizeKB) / 1048576.0 AS DECIMAL(18,2)) AS [索引大小(GB)]   -- 修正命名
FROM (SELECT COALESCE(SUM(a.total_pages), 0) * 8 AS TotalSpaceKB,COALESCE(SUM(a.used_pages), 0) * 8 AS UsedSpaceKB,COALESCE(SUM(a.total_pages) - COALESCE(SUM(a.used_pages), 0), 0) * 8 AS UnusedSpaceKB,MAX(CASE WHEN i.index_id IN (0,1) THEN p.rows ELSE 0 END) AS RowCounts,-- 修正：数据大小 = 堆+聚集索引的空间COALESCE(SUM(CASE WHEN i.type <= 1 THEN a.used_pages * 8 ELSE 0 END), 0) AS DataSizeKB,-- 修正：索引大小 = 非聚集索引的空间COALESCE(SUM(CASE WHEN i.type > 1 THEN a.used_pages * 8 ELSE 0 END), 0) AS IndexSizeKBFROM sys.tables tINNER JOIN      sys.schemas s ON t.schema_id = s.schema_idINNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idLEFT JOIN sys.allocation_units a ON p.partition_id = a.container_idGROUP BY t.Name, s.Name
) AS TableStats;

执行结果：