SQL Script: Size of all tables on all databases

Sometimes I get emails from dbas saying our database is filling up and I want to know where the biggest problems are. I'm sure there are some DBA tools to help with that, but I find a really quick easy way is to execute sp_executesql for every table in every database.

Here's a script you can run to get all table sizes on all databases.

It basically loops over all database (except those you don't care about, e.g. master, msdn, etc.) and gets the table sizes for each table.

At the end, you can then aggregate things by database, or drill into a specific database for table-level details.

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