TSQL Script to get the space used by every table in a SQL Server 2005 database

Here is a TSQL script (developed, tested and used only on Microsoft SQL Server 2005) to get the space used by the tables of a database.

declare @sql nvarchar(MAX)
create table #usedSpace (
name nvarchar(128),
rows varchar(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18))
declare @table nvarchar(MAX)
declare tabcur cursor for 
 select s.[name] +'.'+ t.[name] as [name] 
from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id 
order by s.[name], t.[name];
open tabcur;
fetch next from tabcur into @table;
while @@FETCH_STATUS = 0
 begin
 fetch next from tabcur into @table;
  select @sql = 'sp_executesql N''insert #usedSpace exec sp_spaceused ''''' + @table + ''''''''
  -- print @sql -- uncomment if you wish to read the sql statements
  exec (@sql)
 end;
close tabcur;
deallocate tabcur;
-- select * from #usedSpace order by cast((left(Data,len(Data)-3)) as int) desc -- uncomment if you wish to get a recordset
drop table #usedSpace
 

Use it at your own risk, uncomment the select (or substitute that with your more appropriate log strategy) and read about sp_spaceused and above all dbcc updateusage before bitching about the quality of the results.