Two Ways To Get Record Counts for All Tables in a Database

Introduction

When working with databases – especially during migration or maintenance tasks – it's crucial to understand the size and scope of your data. One fundamental metric is knowing how many records exist in each table. This article explores 2 straightforward methods to obtain record counts across all tables in a SQL Server database, providing database administrators and developers with practical solutions for this common requirement.

The 2 Methods

We are looking to get a row count for every table in the database. There is no magic shortcut here; to find the row count, you need to query the table.

The 2 methods that we will examine are using INFORMATION_SCHEMA along with a cursor and dynamic SQL and using the sp_MSforeachtable procedure to do the same thing.

Both methods approach the data in the same way: they assemble a list of tables and execute a statement against each one. Neither method has any great advantage over the other as far as timing, but the sp_MSforeachtable requires only one statement to complete.

Setting Up

We are going to create 2 temporary tables; one to hold information from each method. Because this article is not about best practices with temporary tables or dynamic SQL, we are going to use global temporary tables to simplify what we are doing. 

SET NOCOUNT ON

DROP TABLE IF EXISTS ##RowCountByCursor
CREATE TABLE ##RowCoUntByCursor(TableName VARCHAR(250), TotalRows BIGINT)

DROP TABLE IF EXISTS ##RowCountByForEach
CREATE TABLE ##RowCountByForEach(TableName VARCHAR(250), TotalRows BIGINT)

DECLARE @ObjectName VARCHAR(150)
DECLARE @SQL NVARCHAR(MAX)

Using INFORMATION_SCHEMA, a Cursor, and Dynamic SQL

The first method will create a cursor of table names in the database and iterate through it. We are selecting the schema and table names and concatenating them together to make the dynamic SQL easier to write.

DECLARE curTables CURSOR LOCAL FOR
SELECT CONCAT('[',t.TABLE_SCHEMA, '].[', t.TABLE_NAME, ']')
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE = 'BASE TABLE'

OPEN curTables

FETCH NEXT FROM curTables INTO @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN

	SET @SQL = 'INSERT INTO ##RowCountByCursor(TableName, TotalRows) SELECT '''+ @ObjectName + ''', CAST(Count(1) as BigInt) FROM ' + @ObjectName
	EXEC sp_executesql @SQL

	FETCH NEXT FROM curTables INTO @ObjectName
END
CLOSE curTables
DEALLOCATE curTables

For each table in the database, the code inserts a record in the temporary table with the object name and the count of rows. 

Using sp_MSforeachtable

The built-in system-stored procedure sp_MSforeachtable does the same thing as the code above, but in one line. It executes the SQL passed to it against every table in the database. The question marks in the statement represent the table name that it is currently executing for.

EXEC dbo.sp_MSforeachtable 'INSERT INTO ##RowCountByForEach(TableName, TotalRows) SELECT ''?'' TableName, CAST(Count(1) as BigInt) NumRecords FROM ?'

The Results

Querying each of the tables produces an identical record set:

SELECT * FROM ##RowCountByCursor ORDER BY TableName
SELECT * FROM ##RowCountByForEach ORDER BY TableName

Both methods return the same information in a similar time frame. You can either get record counts using the INFORMATION_SCHEMA to build query strings, or use sp_MSforeachtable to cut down on what you need to program.