As database developer or BI developer you may get below questions many times:
1) Is our Pre-Production database has exact match with production database?
2) How do we ensure Test environment has good data quality?
3) Is our Test environment has big deviation than production system? and so on.
To answer those questions you have different ways of investigating the database tables. However, one most obvious and first step could be counting rows of two different databse environemnts. You may think it's going to take a lot hell of time to count all the tables's row count in two different environments and list them manually.
well, you don't need to do this task manually. There is a way where you can get row counts of all the tables in a database with small piece of code.
Please run the below code to find number of rows for each table in the chosen database.
SELECT @@SERVERNAME AS Server_Name,
DB_NAME() AS Current_Database,
SCHEMA_NAME(A.schema_id) + '.' + A.Name as Table_Name,
SUM(B.rows) AS Row_Count
FROM sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
GROUP BY A.schema_id, A.Name
I have used AdventureWorks2012 databse and result set looks like below:
|Fig 1: result set of the query|
At the same way you can find out number of rows of the tables across different database environments. Now you can place the result side by side in excel and easily compare the row numbers and number of tables between two different database environments. If you wish to make this automated then you can put this SQL code in a SSIS package and load the data after comparison.