Technical Article

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.

Just replace the database name using CTRL+SHIFT+M and let 'er rip!

use <databaseName, sysname, >
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.dbo.#Tabs') is not null drop table #Tabs
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
        end catch

        select @RID += 1
    
    end


select *
from #Tabs
order by nRows desc

Rate

4 (3)

Share

Share

Rate

4 (3)