wuzhensong

获取MSSQL表所占用空间大小的SQL

SELECT tbl.name,(v.low/1024.0)*ISNULL((
select SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id where i.object_id = tbl.object_id),0.0) AS [DataSpaceUsed] FROM sys.tables as tbl
join master.dbo.spt_values v on 1=1 and v.number=1 and v.type='E'
where SCHEMA_NAME(tbl.schema_id)='dbo'
order by DataSpaceUsed desc