To see sizes of all tables in SQL 2005 use this:

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;