exec sp_msforeachtable @command1='insert #TempTable exec sp_spaceused ''?'''

ORDER  BY data DESC;

ORDER BY CONVERT(INT, replace(data,' KB','')) desc;

DECLARE @tableName VARCHAR(1000); 
CREATE TABLE #AllTables
  (
     row_num    INT IDENTITY(1, 1),
     table_name VARCHAR(1000)
  );

--Using temp table, i dont like to use cursors
INSERT INTO #AllTables
            (table_name)
SELECT s.[NAME] + '.' + t.[name]
FROM   sys.Tables t, sys.schemas s
WHERE  t.[SCHEMA_ID] = s.[SCHEMA_ID]

CREATE TABLE #TempTable
  (
     tableName  VARCHAR(100),
     [rows]     VARCHAR(100),
     reserved   VARCHAR(50),
     data       VARCHAR(50),
     index_size VARCHAR(50),
     unused     VARCHAR(50)
  )

DECLARE @i INT;
DECLARE @tableCount INT;

SELECT @i = 1;
SELECT @tableCount = (SELECT COUNT(1) FROM   #AllTables );

--Loop to get all tables
WHILE ( @i <= @tableCount )
  BEGIN
      SELECT @tableName = table_name
      FROM   #AllTables
      WHERE  row_num = @i;

      --Dump the results of the sp_spaceused query to the temp table
      INSERT #TempTable
      EXEC sp_spaceused @tableName;

      SET @i = @i + 1;
  END;

--Select all records so we can use the reults
SELECT *
FROM   #TempTable
ORDER  BY data DESC;

--Final cleanup!
DROP TABLE #TempTable

DROP TABLE #Alltables;