Blog Detail

SQL SERVER - Query to compare number of Rows between different Databases
13 Apr 2009
Tejas Shah
We have two databases in the same SQL server instance. Both of the databases are copy of the production database, so both contains same tables. We added some records in Test Database’s some tables to update some features. We have added data in many tables of test database. Now we need to also update Production DB with the updated data. To update Production Database, we need to make sure in which tables we have updated data and then we will check and update production database accordingly. We have many numbers of tables, and we have updated many tables, so its not possible for us to check it manually.  So, we need to make query to compare rows of each table with the another database tables, to find out which tables has different rows then the original database. 
Solution:
As I need to solve this problem, I write a query which will give me Rows of each table in one Database. As I need to compare it with another database I write the following query to come out with the solution.  
Example: 
I have two databases. Database A and Database B. 
I need to make a report, which will give me details of each table and rows. 
I made this Stored Procedure in Database A.  
CREATE PROC CompareRowsBetweenDatabas
AS
WITH A AS(
SELECT 
    sch.name AS SchemaName,
    st.Name AS TableName,
    SUM(  
        CASE  
            WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
            ELSE 0  
        END  
       ) AS Rows
     FROM sys.partitions p
     INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
     INNER JOIN sys.tables st ON st.object_id = p.Object_ID
     INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
     GROUP BY st.name, sch.name
),
b as(
SELECT 
    sch.name AS SchemaName,
    st.Name AS TableName,
    SUM(  
        CASE  
            WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
            ELSE 0  
        END  
       ) AS Rows
     FROM B.sys.partitions p
     INNER JOIN B.sys.allocation_units a ON p.partition_id = a.container_id
     INNER JOIN B.sys.tables st ON st.object_id = p.Object_ID
     INNER JOIN B.sys.schemas sch ON sch.schema_id = st.schema_id
     GROUP BY st.name, sch.name
)
SELECT    a.SchemaName, a.TableName, a.Rows,
        b.SchemaName As BSchemaName, b.TableName AS BTableName, B.Rows AS BRows
FROM a 
INNER JOIN b ON a.TableName = b.TableName
    AND a.SchemaName = b.SchemaName
EXEC CompareRowsBetweenDatabase

This will give me results as I need.
Let me know if it helps you.

Tags

DBA Row Count sp_MSforeachtable Compare Database Iterate Each Table

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.

Read More

Categories

Recent Post