This script will create a temp table and store the name of the database table and total row counts for each table in the database.

CREATE TABLE #TableRowCounts
(
TableName varchar(150),
TotalRows int
)

EXEC sp_MSForEachTable @command1=’INSERT #TableRowCounts (TableName, TotalRows) SELECT ”?”, COUNT(*) FROM ?’

SELECT TableName, TotalRows FROM #TableRowCounts ORDER BY TableName, TotalRows DESC

DROP TABLE #TableRowCounts

More on sp_MSForEachTable