04-SQLServer查看当前库下的表大小

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、结果示例