-- List Size Of All Tables Within Database.sql
USE AdventureWorks
GO

--create a temporary table to hold the results
CREATE TABLE #TableStats
(
    [name]       VARCHAR (128),
    [rows]       VARCHAR (128),
    [reserved]   VARCHAR (128),
    [data]       VARCHAR (128),
    [index_size] VARCHAR (128),
    [unused]     VARCHAR (128)
);


GO
EXECUTE sp_msforeachtable "INSERT INTO #TableStats EXEC sp_spaceused '?'"

SELECT   *
FROM     #TableStats
ORDER BY LEN([reserved]) DESC, [reserved] DESC; --sort by space used

DROP TABLE #TableStats;