Get Row Count Of All The Tables In SQL Server Database

Generally most of the times, People who are working as Database Developers or DBA’s will come across to Get Row Count of all the tables in Database to check if there are any table is having 0 records (Or) to Get TOP 5 huge Row Counts tables for a particular Database (Or) To compare the Row counts between Source and Target database after Backup or Data load

Most of the times naïve (Inexperienced) People will try to use create SELECT COUNT(*) FROM [Table] for each table and then they will take the Row Counts for each table.

So for e.g. if we have a Database with 200 Tables then we have to write 200 SELECT COUNT(*) statements and then need to execute those statements. Finally we have to take those results into single place that could be EXCEL or any Flat file to see the each the table row counts. To complete all these things it will take around 1 hour.

We are all known time is so precious. Why can’t we do it in 2-3 minutes of the same task by using some of the SQL SERVER system tables?   

Here we go… 

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 <2 GROUP BY sc.name,ta.name ORDER BY  RowCnt desc

Result:

Tags: sql query to get row count for all tables, count number of rows in each table, count number of rows in each table