-- Script to report space used per table in a database

CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

DECLARE @Counter int
DECLARE @Max int
DECLARE @Table sysname

SELECT  name, IDENTITY(int,1,1) ROWID
INTO       #TableCollection
FROM    sysobjects
WHERE xtype = 'U'
ORDER BY lower(name)

SET @Counter = 1
SET @Max = (SELECT Max(ROWID) FROM #TableCollection)

WHILE (@Counter <= @Max)
    BEGIN
        SET @Table = (SELECT name FROM #TableCollection WHERE ROWID = @Counter)
        INSERT INTO #SpaceUsed
        EXECUTE sp_spaceused @Table, @updateusage = N'TRUE';
        SET @Counter = @Counter + 1
    END

SELECT * FROM #SpaceUsed

DROP TABLE #TableCollection
DROP TABLE #SpaceUsed