Skip to main content

Easiest way to find row count of each sql tables using queries

I was working in a project where I need to compare my Production database and Staging database each time I upload code to the production server.
Being developer it is tedious task to check manually for each table in source and target database as it is taking more time. Using script/query which can get the number of rows from all the tables in selected database can be really effective.

So I was thinkg to check it using query where we can run the query again each database and it returns name of the table and number of rows for each table.
I have prepared a script for that and now let me share that query with my blog readers in this article.

In SQL Server there are tables called sys.tables, sys.sysindexes and many others like this, which stores table related data and all the db object related data for the internal use. So we can write the query on that object to retrieve table rows as below.

MS-SQL Query

objects.rows AS [ROW_COUNT]
FROM sys.tables AS Tables
INNER JOIN sys.sysindexes AS objects
ON Tables.object_id =
AND objects.indid < 2
ORDER BY objects.rows DESC

Please check the below image, it is sample output from AdventureWorks2014 database.

It is as simple as above query. In this system related tables there are more useful information stored so we will see those tips and tricks soon.

Please share you feedback using comment.

Tarun Dudhatra

Tarun is a writer of website. He is a software professional with hands on 10+ years of experience. Generally he writes an article about microsoft technology, Javascript, SQL Server and JQuery. Here he shares his notes, experiences, examples.