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.