How to get the table row count for all tables in a SQL Server database (T-SQL)

Introduction

This T-SQL script will demonstrate how to get the table row count for all tables in a SQL Server database.  

Scenarios

As many people asked how to get the table row count for all tables in a SQL Server database, we provide this script for reference. Getting the row count is based on a condition: index_id < 2 (0: heap, 1: clustered index).

 

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
2. Copy the code from GetTableRowsCount.sql, paste it in a new query and run the script.

After the script finishes running, we’ll get the following figure:

Here are some code snippet for your reference: 

SELECT sc.name +'.'+ ta.name AS TableName
 ,SUM(pa.rows) AS CountRows
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id <2
 GROUP BY sc.name,ta.name

SELECTsc.name +'.'+ ta.nameASTableName 
 ,SUM(pa.rows) ASCountRowsFROMsys.tablestaINNERJOINsys.partitionspaONpa.OBJECT_ID = ta.OBJECT_IDINNERJOINsys.schemasscONta.schema_id = sc.schema_idWHEREta.is_ms_shipped = 0ANDpa.index_id <2GROUPBYsc.name,ta.name

Prerequisites

SQL Server 2005 or higher version