Tüm Kullanıcı Veritabanlarındaki Tüm Tabloların Boyutunu Bulma

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