How to Get Row Count of All Tables of a Database

Last week, I had a requirement to check the row count of all tables having a specific schema of databases. I did some search on this because my database has thousands of tables and it was not possible to run SELECT COUNT (*) for each table individually. Here i will explain how to get row counts of all:

• Tables
• Heap tables only
• Clustered index tables only
• Tables having specific Schema

We can get total row counts of all tables using system catalog view sys.partitions or DMV sys.dm_db_partition_stats. Read attached article if you are getting wrong row counts using this DMV or catalog view. Let’s check the row counts using both ways in this tip.

Get Row Counts of Tables using sys.partitions

System catalog view sys.partitions contains a row for each partition of all the tables and most types of indexes in the database. Special index types such as Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned. Run below script to get the row count of all tables in a database.

--Run below script to get the row counts of all tables in a database.
SELECT      SCHEMA_NAME(O.schema_id) + '.' +SELECT      SCHEMA_NAME(O.schema_id) + '.' + O.name As [Schema.TableName],  SUM(P.rows) AS [Total_RowCount] 
FROM        sys.objects O
INNER JOIN sys.partitions P ON O.object_id = P.object_id
WHERE     O.type = 'U' AND P.index_id<2  
GROUP BY    O.schema_id, O.name
ORDER BY [Total_RowCount] desc

You can see row count of all tables in below screenshot.

We can get total row count of all the heap tables or for all the clustered index tables as well by changing the value of index_id mentioned in WHERE clause. Index_id value indicates the ID of the index within the object/table. If index_id value is 0 that means that table doesn’t have any cluster index and a Heap table.

• 0 = heap
• 1 = clustered index
• 2 or greater = nonclustered index

We can pass index_id value in above script to get the total row count of all tables whether it is heap or clustered index table. Run below scripts to get the row count of all heap tables.

--Run below script to get the row counts of all Heap (tables without cluster index) in a database.
--Change the value of index_id from 0 to 1 to get total row count to cluster index tables.
SELECT      SCHEMA_NAME(O.schema_id) + '.' +
        O.name As [Schema.TableName], SUM(P.rows) AS [Total_RowCount]
FROM        sys.objects O
INNER JOIN sys.partitions P ON O.object_id = P.object_id
WHERE     O.type = 'U'
  AND P.index_id=0  --Change this value according to your need
GROUP BY    O.schema_id, O.name
ORDER BY [Total_RowCount] desc

You can see there are only two tables that have no cluster index in above screenshot. We can also get the row count of all tables having a specific schema by adding a condition in WHERE clause.

--Run below script to get the row count of all tables having dbo Schema in a database.
SELECT      SCHEMA_NAME(O.schema_id) + '.' +
        O.name As [Schema.TableName], SUM(P.rows) AS [Total_RowCount]
FROM        sys.objects O
INNER JOIN sys.partitions P ON O.object_id = P.object_id
WHERE     O.type = 'U'
  AND P.index_id<2  
  AND SCHEMA_NAME(O.schema_id) ='dbo'--Change dbo with your schema name for which you need row counts
GROUP BY    O.schema_id, O.name
ORDER BY [Total_RowCount] desc

You can see the output in below image. You can make changes as per your need.

Get Row Count of All Tables using DMV sys.dm_db_partition_stats

DMV sys.dm_db_partition_stats returns page and row-count information for every partition in the current database. We will leverage this DMV to get row count details of all tables. Run below script to get the row count of all tables using this DMV.

--Run below script to get the row count of all tables in a database.
SELECT QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) AS [Schema.TableName]
      , SUM(p_dmv.row_count) AS [Total_RowCount]
FROM sys.objects AS O
      INNER JOIN sys.dm_db_partition_stats AS p_dmv
            ON O.object_id = p_dmv.object_id
WHERE  O.type = 'U'
        AND p_dmv.index_id < 2
GROUP BY
      O.schema_id
      , O.name
ORDER BY [Schema.TableName]
GO 

We can see the output of above script in below image.

We can pass index_id value in above script to get the total row count of all tables whether it is heap or clustered index table. Run below scripts to get the row count of all heap & clustered index tables.

--Run below script to get the row counts of all Heap (tables without cluster index) in a database.
--Change the value of index_id from 0 to 1 to get total row count to cluster index tables.
--Change the schema name if you want the row count of tables that are having specific schema.
SELECT QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) AS [Schema.TableName]
      , SUM(p_dmv.row_count) AS [Total_RowCount]
FROM sys.objects AS O
      INNER JOIN sys.dm_db_partition_stats AS p_dmv
            ON O.object_id = p_dmv.object_id
WHERE  O.type = 'U'
        AND p_dmv.index_id < 2
 AND SCHEMA_NAME(O.schema_id) = 'dbo'--Change the name of your Schema to get row count of all tables having this schema.
GROUP BY
      O.schema_id
      , O.name
ORDER BY [Schema.TableName]
GO 

And here is the output:

I hope you like this article. You can comment about your questions in comment section. Please follow us on our facebook page and on Twitter handle to get latest updates.

Related Articles:

• Why rou count showing incorrect value in sys.partitions?
• Understanding Different Types of SQL JOINs with Examples
• Create a Logon Trigger to Restrict sysadmin logins to Connect to SQL Server during a given time Interval
• How to Protect Stored Procedure Code/Script?