SQL Script to Get Row Count of All Tables in Database

There is an undocumented built-in stored procedure than iterates over all tables in a database and executes a SQL script "sp_msforeachtable" which makes the job pretty straightforward:

sp_msforeachtable "SELECT '?', COUNT(*) FROM ?"

Another nifty little sproc to use is the sp_spaceused which will besides row count return reserved size, data size, index size and unused size of a given table. Combine the two and you get a pretty nice list:

CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
GO

EXEC sp_msforeachtable "INSERT #TempTable EXEC sp_spaceused '?'"
GO

SELECT * FROM #TempTable

DROP TABLE #TempTable
GO