List Table Rowcount and Size

Here's a handy way to get a list of table rowcounts and space used. If you've not used the sp_msforeachtable just plug in the question mark (?) as a token for the name of the table.

--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
--loop through each table and populate the temp table
--NOTE: we can't use table variables with the sp_msforeachtable
sp_msforeachtable "INSERT INTO #TableStats EXEC sp_spaceused '?'"

SELECT * FROM #TableStats
--ORDER BY LEN([rows]) DESC, [rows] DESC --sort by # of rows
ORDER BY LEN([reserved]) DESC, [reserved] DESC --sort by space used
DROP TABLE #TableStats
GO