2008年8月7日 星期四

MSSQL 查所有 Table 使用的空間

筆記一下,下面的指 令可以查所有 Table 使用的空間(單位是k)

declare @PageSize float
select @PageSize=1024 from master.dbo.spt_values v where v.number=1 and v.type='E'

select object_id, object_name(object_id) as name, (
select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < class="keyword">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 = o.object_id) data,
(select @PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < class="keyword">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 = o.object_id) [index]
from sys.objects o
order by 3 desc

0 回應:

 
TEMPLATE HACKS AND TWEAKS BY [ METAMUSE ] BLACKCAT 1.1