Scott Mitchell gave some instructions on 4GuysFromRolla a while back on how to list the sizes of all the tables in a SQL Server database.
His solution uses a mixture of SQL and ASP.NET, but some people will want an alternative in pure SQL. So, with no further ado, here you go:
[code]
create procedure sp_get_table_usage
as
begin
create table #t (
name varchar(100),
rows int,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100)
)
declare @name varchar(100)
declare c cursor
for select name from sysobjects where type=’U’
open c
fetch next from c into @name
while @@FETCH_STATUS = 0
begin
insert into #t
exec sp_spaceused @name
fetch next from c into @name
end
close c
deallocate c
update #t
set reserved = rtrim(replace(reserved, ‘KB’, ”)),
data = rtrim(replace(data, ‘KB’, ”)),
index_size = rtrim(replace(index_size, ‘KB’, ”)),
unused = rtrim(replace(unused, ‘KB’, ”))
alter table #t
alter column reserved int
alter table #t
alter column data int
alter table #t
alter column index_size int
alter table #t
alter column unused int
select * from #t
order by name
end
[/code]
You can change the sort order by changing the order by clause at the end. For instance, order by data desc will list them in descending order of size.