Count No of records in all table using T-SQL

In this article we will learn to fetch the numbers of rows in ms sql database  table.

Some time in situations where we require 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 we do not need to worry on much same. SQL is keeping all this information ready for us, only we have to search the same at correct place.

"sys.partitions" is the table in MS SQL server database which maintain all records count .  However it is very difficult to know the table since SQL store this information by refering  the objectid instead of table name.

So to get the tables names from object we need to refer the system define tables sys.tables where it store the object name and id.

Provided below are the sample queries for the same

SELECT sc.name +'.'+ ta.name TableName, SUM(pa.rows) 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.nameO RDER BY SUM(pa.rows) DESC

NOTE: This query in not  compatible on Azure DB so we 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.