How to Count Records in MS SQL Server Database Tables

Discover how to count records across all tables using Cursor and Temporary Table. Download code to streamline your database analytics and management tasks

Dynamic Record Counting Script

Understanding data spread across tables is essential for analytics and maintenance in large databases.

The following T-SQL script counts records in each table of a specified schema and sorts the results by record count in descending order.

DECLARE @SchemaName NVARCHAR(255) = 'dbo'; -- Set the schema name here
DECLARE @CurrentTableName NVARCHAR(255); -- The name of the current table being processed
DECLARE @DynamicSQL NVARCHAR(MAX);       -- Dynamic SQL command to be constructed and executed
DECLARE @ResultsTable TABLE (TableName NVARCHAR(255), RecordCount INT); -- Table to store results

-- Cursor to iterate through all tables in the specified schema
DECLARE TableCursor CURSOR FOR 
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = @SchemaName;

OPEN TableCursor;

FETCH NEXT FROM TableCursor INTO @CurrentTableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct the SQL query to count records in the current table
    SET @DynamicSQL = 'SELECT ''' + @CurrentTableName + ''' AS TableName, COUNT(*) AS RecordCount FROM [' + @SchemaName + '].[' + @CurrentTableName + ']';
 
    -- Insert the result into the @ResultsTable
    INSERT INTO @ResultsTable (TableName, RecordCount)
    EXEC sp_executesql @DynamicSQL;

    FETCH NEXT FROM TableCursor INTO @CurrentTableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

-- Select final results, sorted by RecordCount in descending order
SELECT * FROM @ResultsTable ORDER BY RecordCount DESC;

Example Output

Selecting MSSQL Tables records count via T-SQL

Key Points

  • Cursors: Loop through tables in the schema and process them individually.
  • Dynamic SQL: Build and execute SQL queries for each table dynamically.
  • Temporary Table: Store and sort record counts for efficient presentation.

Concluding Insights#

This script is a straightforward tool for database analytics, providing quick insights into table record counts.

Modify the @SchemaName variable to fit your needs.

The code is available on GitHub

Be cautious when using it on a production database, as performance may be impacted, especially with large tables or high concurrency.

↑ Top ↑