Skip to main content

SQL Server query to get quick table size information.
There are many reasons you might want to see the sizes of all tables on a database. Maybe you're part of an effort to try to trim down some of the more egrigious tables out there. Maybe you're doing index optimization and want to see how much space indexes are taking up in general. Maybe you're testing cleanup processes across a bunch of tables, and you want to quickly see the effect the cleanup has had.
There are many ways to get at table size information, but usually the easiest is sp_spaceused. The only drawbacks are it can only do one table at a time, and several of the values it returns are strings instead of numbers. This tends to be my go-to script for these sorts of queries because it's quick to run, lets me query the data afterwards, and when bound to some sort of shortcut, I can pull the information up at any time.

use <DATABASE_NAME_HERE>;

set nocount on;
go

declare @TableName varchar(128),
    @RID int,
    @MaxRID int;

declare @loopSrc table(
    RID int identity(1, 1) primary key clustered,
    TableName varchar(128)
);

if object_id('tempdb..#Tabs') is not null
    begin
        drop table #Tabs
    end;

create table #Tabs(
    TableName varchar(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)
);

/*
****************************
*** INSERT LOOP ITEMS HERE ***
****************************
*/

insert into @loopSrc(TableName)
    select name from sys.tables;

select @RID = 1, @MaxRID = @@rowcount;

/*---------------------
*********************
*** LOOP STRUCTURE  ***
*********************
---------------------*/

while @RID <= @MaxRID
    begin
        select @TableName = TableName
        from @loopSrc
        where RID = @RID;

        begin try
            insert into #Tabs
                exec sp_spaceused @tableName;
        end try

        begin catch
           print N'error occured.';
        end catch;

        select @RID+=1;
    end;

select * from #Tabs order by nRows desc;

if object_id('tempdb..#Tabs') is not null
    begin
        drop table #Tabs
    end;