查询 sql 数据库中各个表所占G得大小

SELECT t.NAME AS TableName,CAST(SUM(p.rows) AS DECIMAL(18,2)) AS [RowCount], --计算该表的总行数（所有分区之和），转为两位小数的数字

　　CAST(SUM(a.total_pages) * 8 / 1024.0 / 1024.0 AS DECIMAL(18,2)) AS [TotalSpaceGB],  --总页数 × 8 字节/页 → 字节 → 除以两次 1024 → GB（实际是 GiB）

　　CAST(SUM(a.used_pages) * 8 / 1024.0 / 1024.0 AS DECIMAL(18,2)) AS [UsedSpaceGB], --已使用的页数 → GB
　　 CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0 / 1024.0 AS DECIMAL(18,2)) AS [UnusedSpaceGB] 　　--总页数 − 已用页数 = 未使用页数 → GB

FROM sys.tables t     --从 sys.tables 中取表名，别名 TableName 

INNER JOIN sys.indexes i ON t.object_id = i.object_id INNER 　　--每个表可能有多个索引（包含堆或聚集索引），通过object_id关联

JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id  --每个索引可能分区（Partition）,通过object_id关联和index_id关联

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id  --每个分区对应存储单元（分配单元）container_id关联到分区

WHERE t.is_ms_shipped = 0 AND i.object_id > 255 GROUP BY t.NAME ORDER BY TotalSpaceGB DESC;