Sometimes I run this... It gets all tables to temp table, loops it through and gets sizes for all tables. Result data is in @tablesizes, so you can query it how you like.
Works in Sql Server >2005
declare @tables TABLE
(
table_name nvarchar(200)
)
declare @tablesizes TABLE
(
[name] nvarchar(200),
[rows] int,
reserved nvarchar(200),
data nvarchar(200),
index_size nvarchar(200),
unused nvarchar(200),
reserved_int int,
data_int int,
index_size_int int,
unused_int int
)
declare @t nvarchar(200)
insert into @tables
select Table_Name from information_schema.tables
while exists(select * from @tables)
begin
set @t=(select top 1 table_name from @tables)
insert into @tablesizes([name],[rows],reserved,data,index_size,unused)
exec sp_spaceused @t
delete top (1) from @tables
end
update @tablesizes set
reserved_int=convert(int, replace(reserved,' KB','')),
data_int=convert(int, replace(data,' KB','')),
index_size_int=convert(int, replace(index_size,' KB','')),
unused_int=convert(int, replace(unused,' KB',''))
select * from @tablesizes order by data_int desc