SQL Server : Reporting space used by all tables in a database sorted by size

Tags : large tabletable space

This script will provide a report containing a list of all user tables in a database and sort them by their size, with largest table being on top.

Versions

1.0

create table #spaceused(name varchar(1000),rows integer,reserved varchar(100),
data varchar(100),index_size varchar(100),unused varchar(100))
select name,schema_id into #tbllist from sys.objects where type='u' order by name
--select * from #tbllist
declare @tblname varchar(1000)
declare @fulltblname varchar(1000)
select @fulltblname=schema_name(schema_id)+'.'+name,@tblname=name from #tbllist
while exists(select name from #tbllist)
begin
	insert into #spaceused exec sp_spaceused @fulltblname
	delete from #tbllist where name = @tblname
	select @fulltblname=schema_name(schema_id)+'.'+name,@tblname=name from #tbllist
end
select * from #spaceused order by convert(integer,substring(reserved,0,charindex(' ',reserved)))desc
drop table #spaceused
drop table #tbllist

    

Report Script

Top Users