Count records in all database tables

Every ones in a while you want to know the record count of all objects in your database. If you have 10 objects or less in your database, you can choose to type out the statements yourself, but once it’s more then that it gets quite annoying.

Because I needed this for a production issue (make sure that all objects contain records after import) I wrote a cursor to query all the database objects, and count the content of it:

USE Sandbox

--==========================================================================================
DECLARE @TableName VARCHAR(50) = '',
        @SQLStatement VARCHAR(MAX) = ''
--==========================================================================================
DECLARE TableCursor CURSOR FOR
SELECT name
FROM sys.tables
WHERE name NOT IN ('sysdiagrams')
AND name NOT LIKE 'Temp%'
AND name NOT LIKE '%ReturnGrid%'
ORDER BY name ASC
--==========================================================================================
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
--==========================================================================================
WHILE @@FETCH_STATUS = 0
BEGIN
       SET @SQLStatement = @SQLStatement + 'SELECT ''' + @TableName + ''' AS TableName,
                                            COUNT(*) AS RecordAmount FROM ' + @TableName
       SET @SQLStatement = @SQLStatement + ' UNION ALL '

       /* Print Result */
       PRINT @TableName

       /* Select SQL Statement */
       --SELECT @TableName, @SQLStatement

       FETCH NEXT FROM TableCursor INTO @TableName
END
--==========================================================================================
CLOSE TableCursor DEALLOCATE TableCursor
--==========================================================================================
SET @SQLStatement = LEFT(@SQLStatement, (LEN(@SQLStatement) - 10))
--==========================================================================================
/* Print  */
PRINT '--=========================================================================================='
PRINT 'Executing Statements'

EXEC(@SQLStatement)
--========================================================================================== 

 
The script uses a cursor to query the object names from the sys.tables, and with that data it creates a dynamic SQL query. That query counts the content of all objects. The sysdiagrams (MS Shipped table), Temp and ReturnGrid tables are excluded from the cursor.

For the big databases I build in a PRINT statement, so you can keep track of the progress while running the query. If the dynamic SQL statement is executed, it will show the text “Executing Statements” via a PRINT statement in the bottom of the query. After that you will see the results.