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.
SELECT Tables.name AS [TABLE_NAME], objects.rows AS [ROW_COUNT] FROM sys.tables AS Tables INNER JOIN sys.sysindexes AS objects ON Tables.object_id = objects.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.