Get total number of rows for all tables

How to get total number of rows for all the tables in a particular database? In this post we are going to learn how we can get “total number of rows for all tables” in a database.

We will use the inbuilt system views of SQL Server to generate this list. Sometimes we need to pick some high record containing tables for some demo or any other purpose.
To get the desired output, first select the database for which you want to extract the list of tables with total number of rows as below;

USE AdventureWorks2012
GO

Now use the below query and have a look on its output;

SELECT SCHEMA_NAME(ST.schema_id) + '.' + ST.Name AS TableName, MAX(SP.rows) AS TotalRows
FROM SYS.Tables ST
INNER JOIN SYS.partitions SP on SP.object_id = ST.object_id
GROUP BY SCHEMA_NAME(ST.schema_id) + '.' + ST.Name
ORDER BY TotalRows DESC

OR

SELECT SCHEMA_NAME(ST.schema_id) + '.' + ST.Name AS TableName, SP.rows AS TotalRows FROM SYS.Tables ST
INNER JOIN SYS.partitions SP on SP.object_id = ST.object_id AND index_id < 2
ORDER BY TotalRows DESC

Output :

From above output, you can see that we have extracted the list of all the tables for a particular database with total number of rows.

Analyze the used query above

In above query, I have used the inbuilt view SYS.Tables to get the list of all the tables then simply applied a join with SYS.partitions system view with a join condition as SP.object_id = ST.object_id. In SYS.partitions view, sql server stores the number of rows for each partition for all the tables with indexes details except Full-text index, Spatial index, XML index.

If you run the below query, the output will be as below;

SELECT * FROM SYS.PARTITIONS ORDER BY OBJECT_ID

Output :

Note : In this output, you will see that the same Object_Id is being repeated multiple times with different index_id throughout.

After applying the JOIN i have used a group by clause as GROUP BY SCHEMA_NAME(ST.schema_id) + ‘.’ + ST.Name to remove the duplicate records from SYS.partitions view, we have multiple records for a single table, i have aggregated the output using MAX and GROUP BY.

Finally, i have used ORDER BY clause as ORDER BY TotalRows DESC to generate the list in descending order as per total number of rows.

But in second query i did not used any aggregate and instead of removing duplicates, i have added a WHERE condition as index_id < 2 to take only one partition’s row count from SYS.partitions view.

What is index_id < 2 ?

index_id is unique for each object (table) and has below detail;
0 = Heap
1 = Clustered index
> 1 = Non clustered indexes

I have used this condition because a table can be created either on HEAP or on B-Tree. In both cases table will have 0 or 1 as it’s index_id and we will not miss any table either it has a clustered index or not.

SCHEMA_NAME() inbuilt function has been used to extract the schema name of belonging table. So in our list we have table names preceded with schema names.

Do like and rate this post if you like this and thanks for your patience and your interest too.