Count No of records in all table using T-SQL

By | March 19, 2014
rx online

In this article we will learn to fetch the numbers of rows in your each table.

Some time in situations you are required just the count of the rows in each table, we can go with various approach like  using third party software, count manually 🙁 or write program in our preferred language.

But no need to worry to much… SQL is keeping all this information ready for you, you just need to call it.

sys.partitions" is the table in your SQL server which is maintaining all records count however it is very difficult to know the table since it is used the objectid instead of table name. So in this case we will join with sys.tables from were you will get the object id of table and then you get the desired results very easily.

Provided below are the sample queries for the same

SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 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 in not compatible on Azure DB you can use below query on Azure Db

SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.row_count) RowCnt
 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

Hopes it is useful for you.

Don’t forget to leave your comments

1,776 total views, 1 views today

Category: SQL