Fetching the Number of Rows in an MS SQL Database Table

In this article, we will learn how to fetch the number of rows in an MS SQL database table.

Sometimes, we need the count of rows in each table. There are various approaches to achieve this, such as using third-party software, counting manually 😞, or writing a program in our preferred language.

However, we don’t need to worry much. SQL keeps all this information ready for us; we just need to search in the correct place.

Using sys.partitions

The sys.partitions table in the MS SQL Server database maintains all record counts. However, it is challenging to identify the table since SQL stores this information by referring to the object_id instead of the table name.

To get the table names from the object, we need to refer to the system-defined table sys.tables, where it stores the object name and ID.

Sample Queries

Here are the sample queries to fetch the row counts:

SELECT sc.name + '.' + ta.name AS TableName, 
       SUM(pa.rows) AS RowCount 
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 IN (1, 0) 
GROUP BY sc.name, ta.name 
ORDER BY SUM(pa.rows) DESC;

Note: This query is not compatible with Azure DB. For Azure DB, use the query below:

SELECT sc.name + '.' + ta.name AS TableName, 
       SUM(pa.row_count) AS RowCount 
FROM sys.tables ta 
INNER JOIN sys.dm_db_partition_stats 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 IN (1, 0) 
GROUP BY sc.name, ta.name 
ORDER BY SUM(pa.row_count) DESC;

I hope this is useful for you