T-SQL: Get Row Counts For Every Table In A Database

Here’s a quick way to get table row counts for all tables in a database using dynamic SQL, the built-in INFORMATION_SCHEMA.TABLES view, and the “magic” of “XML PATH(”)”:

 
DECLARE @TABLES TABLE (TableName NVARCHAR(1000))
 
INSERT @TABLES
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
 
DECLARE @SQL NVARCHAR(MAX)
 
SET @SQL = (
		SELECT 'SELECT COUNT(*) AS [' + TableName + '] FROM ' + TableName + '; ' + CHAR(10)
		FROM @TABLES
		ORDER BY TableName
		FOR XML PATH('')		
	)
 
EXEC sp_executesql @SQL

Hope this helps!

This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

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