Tuesday, February 14, 2012

Space used to store data in Table and Index

create table #t
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)

declare @id nvarchar(128)

declare c cursor for
SELECT name FROM sys.tables
WHERE SCHEMA_NAME(schema_id) = 'dbo' AND type='U'
open c
fetch c into @id

while @@fetch_status = 0 begin

insert into #t
exec sp_spaceused @id

fetch c into @id

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

No comments:

Post a Comment