April 26, 2009

SQL SERVER - Query to compare number of Rows between different Databases



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.




6 comments:

  1. what is B.sys.partitions here

    ReplyDelete
  2. Hi,

    "B" refers to the Database: B. It should be another database which you want to compare.

    e.g.: If your databasename: Pubs, it should be Pubs.sys.partitions

    Let me know if you have any problem

    Thanks,
    Tejas
    SQLYoga.com

    ReplyDelete
  3. Thank you so much it worked. Great Article.

    Thanks
    Shriram. Bangalore

    ReplyDelete
  4. Thanks for your efforts Tejas.

    It works like a charm.

    All the best,
    Dejan

    ReplyDelete
  5. HI,
    i have created 2 database A and B. both are having same tables. In A database i have created one more Table which is not exist in the B Database.
    now i am executing your Proc. its running successfully.

    But my question is that will it tell the difference of tables too which is not exist in another Database?

    ReplyDelete
  6. hi,
    I have a similar task in oracle and I am not able to use this procedure. can you please help me with oracle code for the same procedure.

    Thanks

    ReplyDelete