TSQL - How to Get Row Count Of All The Tables In SQL Server Database

Sometime we want to get the Row Count quickly for all the tables in our Database for analysis. There are multiple ways to do that. We can use TSQL Cursor to loop through all the tables and use count(*) to get the row count.

I am using Catalog views to get this information by using below query. This is quick way to find the row count for all the tables in a database. The Cursor with Count(*) can be slow as it has to count rows for each of the table.


USE YourDBName
GO
SELECT OBJECT_NAME(id) AS TableName,
       rowcnt          AS [RowCount]
FROM   sysindexes s
       INNER JOIN sys.tables t
               ON s.id = t.OBJECT_ID
WHERE  s.indid IN ( 0, 1, 255 )
       AND is_ms_shipped = 0

I ran above query on TestDB and got below information.

Fig 1: Get Row Count for all the Tables in SQL Server Database


Check out our other posts on Data Analysis / Data Validation / Data Cleansing

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)