UmbraCare LOGO

How to Count Records in MS SQL Server Database Tables

Discover how to count records across all tables within a specific MS SQL Server database schema. This guide provides you with ready-to-use code, streamlining your database analytics and management tasks efficiently.

Published on: , by:
Piotr Bach
Piotr Bach
Wojciech Tengler
Wojciech Tengler
MS SQL

The Challenge of Large Databases

Large databases pose performance and complexity challenges, making tasks like counting records across multiple tables daunting without the right tools.

Solution: A Dynamic T-SQL Script

Below is a T-SQL script that dynamically counts records across all tables in a specific schema, offering flexibility and efficiency.

This script counts the number of records in each table within a specified schema of an MSSQL database and sorts the results in descending order by the record count. 

You can set the schema name by modifying the @SchemaName variable.

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;

Here is example output of the script:

Select MSSQL Tables records count via T-SQL

Core Concepts: Cursors, Dynamic SQL, and Temporary Table

Cursors

The script uses a cursor to iterate through the INFORMATION_SCHEMA.TABLES view, systematically accessing each table within the specified schema.

This method ensures that every table is processed individually, facilitating detailed data analysis and manipulation.

Dynamic SQL

The script can dynamically adjust to target each table it processes by constructing SQL queries as strings.

This flexibility allows it to count records across varying table structures without the need for hard-coded query parameters, enhancing adaptability and scalability.

Temporary Table

The script employs a temporary table to compile the results of record counts from each table.

This approach organizes the output for easy retrieval and analysis and efficiently manages the data within the script's execution scope, optimizing performance and resource usage.

Concluding Insights

By employing this script, you can efficiently analyze and optimize your data landscape, paving the way for more informed decision-making and streamlined operations. 

The above script can be a valuable addition to your MS SQL toolkit, fostering a deeper, more nuanced understanding of your database's structure and content. 

The code is also available on GitHub.

🌐 Explore More: Interested in learning about SQL and web development insights?

Explore our blog for a wealth of information and expert advice.

Related articles

↑ Top ↑