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;