/*
DB Table Space Usage
Chris Dickey
www.TuneSQLServer.com
*/

create table #t(name nvarchar(50), rows char(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))
set nocount on
exec sp_spaceused
go
declare @sql nvarchar(1024), @tableName nvarchar(100)
declare table_cursor  cursor for
 select cast(u.name + N'.' + o.name as varchar(50))  ObjectName
 from sysusers u, sysobjects o, sysindexes i
 where u.uid = o.uid and i.id = o.id and  o.name not like '#%'
 and  OBJECTPROPERTY(o.id, N'IsMSShipped') <> 1 and OBJECTPROPERTY(o.id, N'IsSystemTable') = 0 
 and i.indid < 2
 order by o.name,u.name

open table_cursor
fetch next from table_cursor
 into @tableName

while @@fetch_status = 0
begin
 set @sql = N'insert #t exec sp_spaceused ''' + @tableName + ''''
 exec (@sql)

fetch next from table_cursor
 into @tableName
end

close table_cursor
deallocate table_cursor

--select * from #t
select name , cast(rows as bigint)'rows',
 cast(substring(reserved,1,len(data)-3) as int) 'reserved'
 ,cast(substring(data,1,len(data)-3) as int) 'data' 
 ,cast(substring(index_size,1,len(index_size)-3) as int) 'index_size'
 ,cast(substring(unused,1,len(unused)-3) as int) 'unused'
 ,cast(substring(data,1,len(data)-3) as int) + cast(substring(index_size,1,len(index_size)-3) as int) 'total_size'
 from #t
-- order by data desc
 order by total_size desc
-- order by rows desc
-- order by index_size desc

drop table #t