Last active
August 6, 2021 13:13
-
-
Save stevewithington/76de827c2119b989c9f7c05fcead54d5 to your computer and use it in GitHub Desktop.
SQL Server Row Count for all Database Tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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