Tüm kullanıcı veritabanlarındaki tüm tabloların KB olarak boyutunu aşağıdaki T-SQL kod ile bulabilirsiniz:
create table #tmptable(DatabaseName sysname,SchemaName sysname, TableName sysname, RowCounts bigint, TotalSpaceKB bigint, UsedSpaceKB bigint, UnusedSpaceKB bigint)
exec sp_msforeachdb 'if db_id(''?'')>4 begin
use [?]
insert into #tmptable
select
db_name() as DatabaseName,
s.Name as SchemaName,
t.name as TableName,
sum((CASE WHEN a.type =1 THEN p.rows ELSE 0 END)) as RowCounts,
sum(a.total_pages)*8,
(sum(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END))*8 as TotalSpaceKB
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
inner join sys.schemas s ON t.schema_id = s.schema_id
where i.index_id in(0,1)
group by s.Name,t.Name
end'
select top 100 * from #tmptable
order by TotalSpaceKB desc
drop table #tmptable
Hiç yorum yok:
Yorum Gönder