How do I run sp_spaceused for all tables in a database? – TechNet Forums

I was looking for a way to list the size of all the tables in database again and I found this nice bit of code. I made a small change on the select statement to create a calculated integer field so I could get a nice descending sort on size. It worked for me!

    
select 'Database Name: ', db_name()

set nocount on

if exists(select name from tempdb..sysobjects where name='##tmp')

drop table ##tmp

create table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))

go

declare @tblname varchar(50)

declare tblname CURSOR for select name from sysobjects where xtype='U'

open tblname

Fetch next from tblname into @tblname

WHILE @@FETCH_STATUS = 0
	BEGIN
	insert into ##tmp
	exec sp_spaceused @tblname
	FETCH NEXT FROM tblname INTO @tblname
	END

CLOSE tblname

deallocate tblname

go

select 
	nam Table_Name
	,rows Total_Rows
	,res Total_Table_Size
	,data Data_size
	,ind_sze Index_Size
	,unsed Unused_Space
	,CAST(replace(res,'KB','') as int) as Total_Table_Size_KB
from ##tmp
ORDER BY Total_Table_Size_KB desc

drop table ##tmp

–Vidhya Saga

Share this:

Related