Here’s a quick and easy script I find myself using often to count all rows across multiple tables, so I thought I would share.
It uses SQL to write, then execute SQL, performing a count(*) across all tables for a defined schema(s), and returns an output with the table name and count.
It is handy for when you need to compare multiple table counts between a source and target database.
Tweak the WHERE clause in line 10 to suit your needs.

DECLARE @TABLECOUNTS NVARCHAR(MAX) ;
SELECT @TABLECOUNTS = COALESCE(@TABLECOUNTS + ‘ UNION ALL ‘,”)
+ ‘SELECT’
+ ”” + QUOTENAME(TAB.TABLE_SCHEMA)
+ ‘.’ + QUOTENAME(TAB.TABLE_NAME) + ”’ AS [TABLENAME]
, COUNT(*) AS [ROWCOUNT] FROM ‘
+ QUOTENAME(TAB.TABLE_SCHEMA)
+ ‘.’ + QUOTENAME(TAB.TABLE_NAME) + ‘ WITH (NOLOCK) ‘
FROM INFORMATION_SCHEMA.TABLES AS TAB
WHERE TAB.TABLE_SCHEMA IN (‘Dimension’,’Fact’)
AND TAB.TABLE_TYPE IN (‘Base Table’)
ORDER BY TAB.TABLE_SCHEMA,TAB.TABLE_NAME
EXEC SP_EXECUTESQL @TABLECOUNTS
GO

Enjoy,
Nic