View space used across multiple tables

Recently, I came across a situation where I had to produce space used across tables in a database. Now my database contains more than 500 tables and to execute sp_spaceused for every table was time consuming. So below is the faster way that can work.


declare @table as varchar(50)

declare @t as table(ID int identity(1,1), tableName varchar(100))

insert @t

select name from sys.tables where type='U' order by name

declare @i as int

set @i=1

while (@i<=(select max(ID) from @t where ID=@i))


set @table=(select tableName from @t where ID=@i)

EXEC sp_spaceused @table

set @i=@i+1



SQL Server