Last active
April 5, 2022 19:47
-
-
Save shaytan1986/f8b32aa1b872c6fdb1d0b49a1662f555 to your computer and use it in GitHub Desktop.
Size of all tables in all databases
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
set nocount, xact_abort on | |
declare | |
@ExclDatabases nvarchar(max) = 'master|msdb|tempdb|ssisdb|model', | |
@Schemas nvarchar(max) = null | |
drop table if exists #AllTabs | |
create table #AllTabs | |
( | |
DbName nvarchar(128), | |
SchemaName nvarchar(128), | |
TableName nvarchar(128), | |
ObjectId int, | |
RowCt bigint, | |
ReservedKB bigint, | |
DataKB bigint, | |
IndexSizeKB bigint, | |
UnusedKB bigint, | |
ReservedGB as (ReservedKB * 1.0) / power(1024, 2), | |
DataGB as (DataKB * 1.0) / power(1024, 2), | |
IndexSizeGB as (IndexSizeKB * 1.0) / power(1024, 2), | |
UnusedGB as (UnusedKB * 1.0) / power(1024, 2) | |
) | |
declare | |
@DatabaseName nvarchar(128), | |
@Sql nvarchar(max) | |
declare c cursor local fast_forward for | |
select name | |
from sys.databases | |
where name not in | |
( | |
select value | |
from string_split(@ExclDatabases, '|') | |
) | |
open c | |
fetch next from c into @DatabaseName | |
while @@fetch_status = 0 | |
begin | |
select @SQL = concat(N' | |
declare | |
@ObjectId int, | |
@SchemaName nvarchar(128), | |
@TableName nvarchar(128), | |
@TwoPartName nvarchar(256), | |
@RID int, | |
@eMsg nvarchar(2000) | |
declare @tabs table | |
( | |
RID int identity(1,1) primary key clustered, | |
ObjectId int, | |
SchemaName nvarchar(128), | |
TableName nvarchar(128), | |
nRows int, | |
nReserved as cast(replace(sReserved, '' KB'', '''') as int), | |
nData as cast(replace(sData, '' KB'', '''') as int), | |
nIndexSize as cast(replace(sIndexSize, '' KB'', '''') as int), | |
nUnused as cast(replace(sUnused, '' KB'', '''') as int), | |
sReserved varchar(30), | |
sData varchar(30), | |
sIndexSize varchar(30), | |
sUnused varchar(30) | |
) | |
begin try | |
declare c cursor local fast_forward for | |
select | |
ObjectId = t.object_id, | |
SchemaName = s.name, | |
TableName = t.Name | |
from ', quotename(@DatabaseName), '.sys.tables t | |
inner join ', quotename(@DatabaseName), '.sys.schemas s | |
on t.schema_id = s.schema_id | |
where @Schemas is null | |
or s.name in | |
( | |
select value | |
from string_split(@Schemas, ''|'') | |
) | |
open c | |
fetch next from c into @ObjectId, @SchemaName, @TableName | |
while @@fetch_status = 0 | |
begin | |
select @TwoPartName = concat(@SchemaName, ''.'', @TableName) | |
begin try | |
insert into @tabs | |
( | |
TableName, | |
nRows, | |
sReserved, | |
sData, | |
sIndexSize, | |
sUnused | |
) | |
exec ', quotename(@DatabaseName), '.dbo.sp_spaceused @TwoPartName | |
select @RID = scope_identity() | |
update @Tabs | |
set ObjectId = @ObjectId, | |
SchemaName = @SchemaName | |
where RID = @RID | |
end try | |
begin catch | |
select @eMsg = error_message() | |
raiserror(@eMsg, 10, 1) | |
end catch | |
fetch next from c into @ObjectId, @SchemaName, @TableName | |
end | |
close c | |
deallocate c | |
end try | |
begin catch | |
select @eMsg = error_message() | |
raiserror(@eMsg, 10, 1) | |
end catch | |
insert into #AllTabs | |
( | |
DbName, | |
SchemaName, | |
TableName, | |
ObjectId, | |
RowCt, | |
ReservedKB, | |
DataKB, | |
IndexSizeKB, | |
UnusedKB | |
) | |
select | |
DbName = @DatabaseName, | |
SchemaName = SchemaName, | |
TableName = TableName, | |
ObjectId = ObjectId, | |
RowCt = nRows, | |
ReservedKB = nReserved, | |
DataKB = nData, | |
IndexSizeKB = nIndexSize, | |
UnusedKB = nUnused | |
from @tabs') | |
exec sp_executesql | |
@sql, | |
N' | |
@DatabaseName nvarchar(128), | |
@Schemas nvarchar(max)', | |
@DatabaseName, | |
@Schemas | |
fetch next from c into @DatabaseName | |
end | |
deallocate c | |
-- Summarize Database sizes | |
drop table if exists #DbSizes | |
create table #DbSizes | |
( | |
DbName nvarchar(128), | |
TableCt int, | |
RowCt bigint, | |
ReservedGB float, | |
DataGB float, | |
IndexSizeGB float, | |
UnusedGB float | |
) | |
insert into #DbSizes | |
( | |
DbName, | |
TableCt, | |
RowCt, | |
ReservedGB, | |
DataGB, | |
IndexSizeGB, | |
UnusedGB | |
) | |
select | |
DbName, | |
TableCt = count(ObjectId), | |
RowCt = sum(RowCt), | |
ReservedGB = sum(ReservedGB), | |
DataGB = sum(DataGB), | |
IndexSizeGB = sum(IndexSizeGB), | |
UnusedGB = sum(UnusedGB) | |
from #Alltabs | |
group by DbName | |
/* | |
declare @Rank int = 0 -- which biggest db you wanna look at? 0 is the biggest, 1 is the second biggest, etc. | |
-- Biggest database breakdown | |
select * | |
from #Alltabs | |
where DbName = | |
( | |
select DbName | |
from #DbSizes | |
order by ReservedGB desc | |
offset @Rank rows | |
fetch next 1 rows only | |
) | |
order by ReservedKB desc | |
*/ | |
select top 1000 * | |
from #DbSizes |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment