Get Record Count for Each Table in Your Database

When working on data migration or performing database audits, one of the most common tasks is to verify the number of records in each table. This step is crucial for planning, estimating migration time, and validating data integrity before and after the migration process.

In this blog post, I’ll show you a simple and effective way to get the row count for every table in your SQL Server database using a temporary table and a handy (though undocumented) stored procedure.

Here’s a snippet of SQL code you can copy, paste, and execute directly in your SQL Server Management Studio (SSMS):

-- Step 1: Create a temporary table to store results

CREATE TABLE #counts

(

    table_name VARCHAR(255),

    row_count INT

);

 

-- Step 2: Use sp_MSForEachTable to iterate over each table

EXEC sp_MSForEachTable

    @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?';

 

-- Step 3: View the results

SELECT table_name, row_count

FROM #counts

ORDER BY table_name, row_count DESC;

 

-- Step 4: Clean up by dropping the temp table

DROP TABLE #counts;

How It Works

·         #counts: This is a temporary table where we store each table's name along with its row count.

·         sp_MSForEachTable: This is a built-in but undocumented stored procedure provided by SQL Server. It loops through each table in the database and executes the given command in this case, counting the rows.

·         SELECT ... ORDER BY: We retrieve the data from our temp table and order it so that we can easily identify tables with the most or fewest rows.

·         DROP TABLE: As a good practice, we drop the temp table once we’re done.

What other ideas can you add to this post that I may have not mentioned?