SQL-Server database table row count

Introduction

Learn how to effectively get row counts for all tables or two or more tables in a single statement for SQL Server. Although the statement does not indicate whether the correct items are in each table, this can be considered a first check. For example, there should be five records in a category table and three in a gender table; this statement should be helpful.

Source code

Below was written in SSMS and refactored for C#.

DECLARE @TableNames TABLE (TableName NVARCHAR(128) NOT NULL);
INSERT INTO @TableNames (TableName)
VALUES ('Categories'),
       ('ContactDevices'),
       ('Countries'),
       ('ContactType'),
       ('PhoneType');

SELECT      TableSchema = s.name,
            Name = t.name,
            [RowCount] = p.rows
  FROM      sys.tables t
 INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
 INNER JOIN sys.indexes i
    ON t.object_id = i.object_id
 INNER JOIN sys.partitions p
    ON i.object_id = p.object_id
   AND i.index_id  = p.index_id
 WHERE      t.is_ms_shipped = 0
   AND      t.name IN ( SELECT TableName FROM @TableNames )
 GROUP BY t.name,
          s.name,
          p.rows
 ORDER BY s.name,
          t.name;

Code

• Written using the Dapper NuGet package, which keeps code to a minimum. Conventional connection and command objects are not required, nor is there an explicit need to open and close connections.
• Code under Classes\Configuration handles reading the connection string from appsettings.json

Using the code

Note
As provided, the code works against a localDb database which is important to know as there is a check to see if the database exists as read from appsettings.json. If not using a local instance of localDb remove the check.

1. Alter the connection string in appsettings.json to point to an existing database.

2. In the method GetRowCountsForSpecificTables replace items in the string array tableNames to names of table in the database set in appsettings.json.

3. Run the project.

The following shows the results from a Northwind database which is included with the project as a script.

Utility code

The following model is used to return results.

public class TableInfo
{
    public string Schema { get; set; }
    public string Name { get; set; }
    public int RowCount { get; set; }
}

Methods to get row counts

class GeneralUtilities
{
    public static async Task<List<TableInfo>> TablesCount(string connectionString)
    {
        const string query =
            """
            SELECT 
                [Schema] = s.name,
                Name = t.name,
                [RowCount] = p.rows
            FROM sys.tables t
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
            INNER JOIN sys.indexes i ON t.object_id = i.object_id
            INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
            WHERE t.is_ms_shipped = 0
            GROUP BY t.name, s.name, p.rows
            ORDER BY s.name, t.name;
            """;

        await using var cn = new SqlConnection(connectionString);

        return (await cn.QueryAsync<TableInfo>(query)).ToList();
    }

    public static async Task<List<TableInfo>> GetTableRowCountsAsync(string connectionString, params string[] tableNames)
    {

        IDbConnection cn = new SqlConnection(connectionString);

        const string sql =
            """
            SELECT 
                [Schema] = s.name,
                Name = t.name,
                [RowCount] = p.rows
            FROM sys.tables t
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
            INNER JOIN sys.indexes i ON t.object_id = i.object_id
            INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
            WHERE t.is_ms_shipped = 0
            AND t.name IN @TableNames
            GROUP BY t.name, s.name, p.rows
            ORDER BY s.name, t.name;
        """;

        return (await cn.QueryAsync<TableInfo>(sql, new { TableNames = tableNames })).ToList();
    }
}

Use in your project

Add NuGet packages for Dapper and Microsoft.Data.SqlClient

Use the code under Classes\Configuration for reading the database connection string from appsettings.json or, however, it's done now.

Summary

The code provided to get record counts can help ensure that tables are populated in an SQL Server database. For ASP.NET Core the code can be used in ValidateOnStart for instance.

See also

EF Core Database/Tables exists