You are here

Microsoft SQL Server - Find Row Count of all the tables

Submitted by Asif Nowaj, Last Modified on 2019-11-22

In this tutorial, we’ll discuss on how to find row count of all the table in one database.

To demonstrate this topic, SQL Server 2008 R2 Express version is being used. The exact version information is

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

You can find your database version information by executing SELECT @@VERSION

We’ll be using AdventureWorks2008R2 here. If you want to get the AdventureWorks database for 2008R2 please browse and download AdventureWorks2008R2-Full Database You need to restore the database to use this.

AdventureWorks 2008 R2 Download Link

For finding the row count of all the tables of a database you can use one Microsoft’s undocumented procedure sp_MSForEachTable.

You can use below query to get all the details

EXEC sp_MSForEachTable @command1='SELECT ''?'', COUNT(*) [Row_Count] FROM ?'

But for better readability we can create a temporary table and inserts all the information into it. Then we can select all the rows from the temporary table to have a better view of the table name and row count.

CREATE TABLE #RowCountsForTables


    TableName varchar(255),

    Row_Count int


EXEC sp_MSForEachTable @command1='INSERT #RowCountsForTables (TableName, Row_Count) SELECT ''?'', COUNT(*) FROM ?'

SELECT TableName, Row_Count FROM #RowCountsForTables ORDER BY TableName, Row_Count DESC

DROP TABLE #RowCountsForTables

It shows the results

Tables Row Count

Discussion or Comment

If you have anything in mind to share, please bring it in the discussion forum here.