Testing in SQL Server – Count all rows from multiple tables

by | Jul 8, 2014

NicHere’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

0 Comments
Submit a Comment

Your email address will not be published. Required fields are marked *