Skip to content

Instantly share code, notes, and snippets.

@stevewithington
Last active August 6, 2021 13:13
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save stevewithington/76de827c2119b989c9f7c05fcead54d5 to your computer and use it in GitHub Desktop.
Save stevewithington/76de827c2119b989c9f7c05fcead54d5 to your computer and use it in GitHub Desktop.
SQL Server Row Count for all Database Tables
-- SQL Server Row Count for all Database Tables
USE [SomeTable]
GO
DECLARE @QueryString NVARCHAR(MAX);
SELECT @QueryString =
COALESCE(@QueryString + ' UNION ALL ','')
+ 'SELECT '
+ '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]'
+ ', COUNT(*) AS [RowCount] '
+ ' FROM '
+ QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name)
+ ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE 1 = 1
AND sOBJ.type = 'U' -- U = USER_TABLE
AND sOBJ.is_ms_shipped = 0x0 -- Not a MS table
AND sOBJ.name NOT LIKE 'temp%' -- Can add additional filters too
AND sOBJ.name NOT LIKE 'tmp%'
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name;
EXEC(@QueryString);
GO
-- https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment