This is a very useful SQL script to listing all of the tables in a SQL Server database with the row count and the amount of space being used by that table, handy for troubleshooting databases that are using more space that expected.
CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
unused VARCHAR(18))
EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '
SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,
CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM #RowCountsAndSizes
ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName
DROP TABLE #RowCountsAndSizes
The original details including some other useful scripts can be found using the following link:
http://www.sqlmatters.com/Articles/Listing%20all%20tables%20in%20a%20database%20and%20their%20row%20counts%20and%20sizes.aspx