Showing posts with label Compare Database. Show all posts
Showing posts with label Compare Database. Show all posts

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.