1、脚本
select t.name as tableName, s.name as schemaName, p.rows as rowCounts, sum(a.total_pages) * 8 as totalSpaceKB, cast(round(((sum(a.total_pages) * 8) / 1024.00),2) as numeric(36,2)) as totalSpaceMB, SUM(a.used_pages) * 8 as usedSpaceKB, cast(round(((sum(a.used_pages) * 8) / 1024.00),2) as numeric(36,2)) as usedSpaceMB, (sum(a.total_pages) - sum(a.used_pages)) * 8 as unusedSpaceKB, cast(round(((sum(a.total_pages) - sum(a.used_pages)) * 8) / 1024.00, 2) as numeric(36,2)) as unusedSpaceMB from sys.tables t inner join sys.indexes i on t.OBJECT_ID = i.OBJECT_ID inner join sys.partitions p on i.object_id = p.object_id and i.index_id =p.index_id inner join sys.allocation_units a on p.partition_id = a.container_id left outer join sys.schemas s on t.schema_id = s .schema_id where t.is_ms_shipped = 0 and i.object_id > 255 group by t.name,s.name,p.rows order by rowCounts desc
2、结果示例
