SQL Server - Other Scripts

Technical Architects Zone (TAZ)

Useful SQL Server Table & Record Count Scripts

Practical T-SQL scripts for listing tables, schemas, and record counts

Introduction

During SQL Server administration, migration, data validation, cleansing, or troubleshooting activities, we often need quick scripts to understand the tables available in a database and their approximate record counts.

This page contains a small collection of reusable T-SQL scripts that can help with common validation activities such as:

• Listing all tables with their schema names
• Checking record counts for all tables
• Checking record counts for selected schemas
• Excluding backup or temporary tables from the result

Contents

1. List All Tables with Schema Name 2. Record Count for All Tables 3. Record Count for Selected Schemas 4. Practical Notes

1. List All Tables with Schema Name

This query lists all user tables in the current database together with their associated schema names.

This is particularly useful when the same table name may exist under different schemas.

SELECT s.name AS SchemaName, t.name AS TableName FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id ORDER BY s.name, t.name;

Example output:
dbo.Customers
dbo.Employees
sales.Orders
staging.Customer_Load

2. Record Count for All Tables

The following query returns the approximate number of rows for every user table in the current database.

It uses SQL Server metadata rather than executing COUNT(*) against every table, which makes it useful for quickly reviewing large databases.

SELECT CONCAT(s.name, '.', t.name) AS TableName, SUM(p.rows) AS RecordCount FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.partitions AS p ON t.object_id = p.object_id WHERE p.index_id IN (0, 1) GROUP BY s.name, t.name ORDER BY RecordCount DESC;

Exclude Backup Tables

In many DBA or data-cleansing activities, backup tables are created using naming patterns such as:

Customer_bckp
Orders_bckp
Employee_bckp

If you want to exclude these tables, add the following condition:

AND t.name NOT LIKE '%[_]bckp%'

Why [_]?
In a SQL Server LIKE pattern, underscore (_) is a wildcard representing any single character. Using [_] matches the underscore character itself.

Complete Query Excluding Backup Tables

SELECT CONCAT(s.name, '.', t.name) AS TableName, SUM(p.rows) AS RecordCount FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.partitions AS p ON t.object_id = p.object_id WHERE p.index_id IN (0, 1) AND t.name NOT LIKE '%[_]bckp%' GROUP BY s.name, t.name ORDER BY RecordCount DESC;

3. Record Count for Selected Schemas

Sometimes you do not need the counts for the entire database. For example, you may only want to validate tables belonging to selected application or staging schemas.

Replace SchemaName1 and SchemaName2 with the actual schema names.

SELECT CONCAT(s.name, '.', t.name) AS TableName, SUM(p.rows) AS RecordCount FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.partitions AS p ON t.object_id = p.object_id WHERE p.index_id IN (0, 1) AND s.name IN ('SchemaName1', 'SchemaName2') GROUP BY s.name, t.name ORDER BY RecordCount DESC;

Selected Schemas and Exclude Backup Tables

The two filters can also be combined. This is particularly useful during data validation or cleansing activities where backup tables should not be included in the comparison.

SELECT CONCAT(s.name, '.', t.name) AS TableName, SUM(p.rows) AS RecordCount FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.partitions AS p ON t.object_id = p.object_id WHERE p.index_id IN (0, 1) AND s.name IN ('SchemaName1', 'SchemaName2') AND t.name NOT LIKE '%[_]bckp%' GROUP BY s.name, t.name ORDER BY RecordCount DESC;

When You Need an Exact Record Count

The metadata queries above are excellent for quickly reviewing many tables. However, if you need an exact count for validation or reconciliation of a specific table, use COUNT_BIG(*) directly against that table.

SELECT COUNT_BIG(*) AS ExactRecordCount FROM dbo.YourTableName;

Performance note: An exact count can require significantly more work on a very large table. Use metadata-based counts for quick inventory checks and use exact counts when reconciliation requires precise row totals.

Practical Notes

1. Always Include the Schema

Instead of referring to a table only as Customers, prefer dbo.Customers. Schema-qualified names remove ambiguity and make validation scripts easier to understand.

2. Heap and Clustered Tables

The condition p.index_id IN (0,1) restricts the metadata calculation to the heap or clustered index, preventing rows from nonclustered indexes from being counted again.

3. Useful During Migration Validation

These scripts are useful when comparing source and target databases during SQL Server migration, data cleansing, restore validation, or environment refresh activities.

4. Row Count Is Only the First Validation

Matching record counts do not guarantee that the data itself is identical. For critical validation, follow the row-count comparison with key-level reconciliation, aggregate comparisons, checksums, or business-rule validation.

Quick Reference

Requirement	SQL Server Object / Method
List tables	sys.tables
Get schema names	sys.schemas
Fast metadata row count	sys.partitions
Heap	index_id = 0
Clustered index	index_id = 1
Exact row count	COUNT_BIG(*)

Key Takeaways

• Use sys.tables and sys.schemas to identify schema-qualified user tables.
• Use sys.partitions when you need a fast metadata-based row count across many tables.
• Filter on index_id IN (0,1) to avoid counting rows from additional indexes.
• Apply schema filters when validation is limited to specific application areas.
• Exclude backup tables when they should not participate in reconciliation.
• Use COUNT_BIG(*) when an exact table-level row count is required.
• Treat matching row counts as an initial validation, not proof that two datasets are identical.

Author: Ajit Kumar Patnaik

Technical Architects Zone (TAZ)

Practical Solutions | Real-World Experience | Continuous Learning