Quick table size information

infrid, 2016-07-04 (first published: 2016-06-08)

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