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))

begin

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

EXEC sp_spaceused @table

set @i=@i+1

END

Tags:

SQL Server