Automating Row Count Retrieval for User Databases

Write a script ensures you get row counts for all tables in all user databases, excluding the system databases.

We can use the undocumented stored procedure sp_msforeachdb, which loops the databases and all calls to every object in the database using sp_msforechtable.

Solution:

— Create a temporary table to store the database name, table name, and row count
CREATE TABLE #TableRowCounts (
[databaseNAme] VARCHAR(100), — Name of the database
[TableName] VARCHAR(128), — Name of the table
[RowCount] INT — Number of rows in the table
);
— Use sp_MSforeachdb to iterate through each database
EXEC sp_MSforeachdb
@command1 = ‘
— Check if the current database is not a system database
IF ”?” NOT IN (”master”, ”model”, ”msdb”, ”tempdb”)
BEGIN
— Insert row count data into the temporary table
INSERT INTO #TableRowCounts ([databaseNAme], [TableName], [RowCount])
EXEC(”USE [?]; EXEC sp_MSforeachtable @command1 = ””INSERT INTO #TableRowCounts ([databaseNAme], [TableName], [RowCount]) SELECT ”””?””” AS [databaseNAme], ”””&””” AS [TableName], COUNT(*) AS [RowCount] FROM &”””” , @replacechar = ”&””)
END’;
— Select the data from the temporary table to view the row counts
SELECT * FROM #TableRowCounts;
— Drop the temporary table to clean up
DROP TABLE #TableRowCounts;