Execute sp_spaceused on ALL tables

Per BOL, sp_spaceused displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.  Very handy, but unfortunately, it has to be executed selectively, one table at a time.  It can be run without the tablename (@objname), but it will return different, general details for the entire database.  This is just a handy method to run it for ALL tables in the database:


  CREATE TABLE #Space (
   tablename    sysname,
   [rows]       int,
   reserved_S   varchar(32),
   data_S       varchar(32),
   index_size_S varchar(32),
   unused_S     varchar(32),
   reserved_KB  as cast(left(reserved_s,charindex(' ',reserved_s)-1) as int),
   data_KB as cast(left(data_S,charindex(' ',data_S)-1) as int),
   index_size_KB as cast(left(index_size_S,charindex('',index_size_S)-1) as int),
   unused_KB as cast(left(unused_S,charindex(' ',unused_S)-1) as int)
  )


  INSERT #Space(tablename,[rows],reserved_s,data_s,index_size_s,unused_s)
  EXEC sp_msforeachtable 'exec sp_spaceused [?]'

  SELECT tablename,[rows],reserved_KB,data_KB,index_size_KB,unused_KB
  FROM #space
  ORDER BY reserved_KB DESC


  DROP TABLE #space


These are the first few results when run against AdventureWorks:


   tablename              rows    reserved_KB   data_KB     index_size_KB     unused_KB 
   Individual                            18484      80984    29608     50616      760 
   SalesOrderDetail                 121317   19528     12216       6560      752 
   TransactionHistory               113443   12336       7840       3904       592 
   SalesOrderHeader               31465     10280       6888      2992        400 
   TransactionHistoryArchive    89253       9856       6176      3136        544 
   WorkOrderRouting               67131       8464       6816      1248        400  
   ........
   ......
   ...