Reporting on space used in a SQL database

March 19, 2010 Ed Goad

I had the need today to look at a database and quickly identify which tables were taking up the most space. Since there are over 400 tables, doing this by hand wasn’t going to work. Below is the TSQL script I created based on the sp_spaceused stored procedure to catalog the data and output it into a result set.

declare @TableName varchar(100)
declare @output table([name] varchar(100), [rows] int, [reserved] varchar(50), [data] varchar(50), [index_size] varchar(50), [unused] varchar(50))
declare TableNames CURSOR FOR
select name from sys.tables order by 1
OPEN TableNames

FETCH NEXT FROM TableNames
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
insert into @output exec sp_spaceused @TableName
FETCH NEXT FROM TableNames
INTO @TableName
END

close TableNames
deallocate TableNames
select * from @output