TSQL: Rowcount for all tables

Why do it this way? To return a single rowset rather than one for each table.

SET NOCOUNT ON

DECLARE
@database_name sysname ,
@sql nvarchar(1000)

DECLARE
@All_Tables_Count_Rows table (Table_Name sysname, Row_Count int)
--for SQL Server 2000 create and drop a table
--CREATE TABLE #All_Tables_Count_Rows (Table_Name sysname, Row_Count int)

SELECT
@database_name = 'msdb'

SELECT
@sql = QUOTENAME(@database_name) + '.[dbo].[sp_msforeachtable] ''SELECT ''''?'''', COUNT(*) FROM ?'''
--SELECT @sql

--INSERT #All_Tables_Count_Rows
INSERT @All_Tables_Count_Rows table
EXEC (@sql)

SELECT Table_Name AS Table_Name , Row_Count AS Row_Count
FROM @All_Tables_Count_Rows
--FROM #All_Tables_Count_Rows
ORDER BY Table_Name ASC

--DROP TABLE #All_Tables_Count_Rows