Compare tables data on different databases with same structure 2


Last night, i was asked to create a script to compare a bunch of tables data from QA to Prod server. Be sure here that i am talking about the comparison of data and nor schema. Some of us also uses some tools to achieve the same and i have no question about their working ability and method. All these tools paid or even free can do much better job than what i am about to share here. But question is still here that in some cases either we don’t want to use these tools or not aware of them.

Again i am saying that in this article i am not raising any question about any tool which does the comparison of table data.

Now just have a look on the below script and at the bottom i will share the description of this script. You can also find the attached file at bottom and download it on your machine.


SET NOCOUNT ON;

DECLARE @Table1_FullName VARCHAR(500) = 'AdventureWorks2012.HumanResources.Employee' --Database then schema then table name (In case of linked server use server name first then all other listed object names)

DECLARE @Table2_FullName VARCHAR(500) = 'AdventureWorksDW2012.dbo.Employee' --Database then schema then table name (In case of linked server use server name first then all other listed object names)

--DECLARE @ColToDisplay  VARCHAR(500) = 'BUSINESSENTITYID' --Comma separated list of columns to display as a comparison result

--Column list will be taken from first table

DECLARE @IsIdentityToCompare BIT  = 1 --Indicate whether to compare identity column if any exists

DECLARE @ColListToExclude VARCHAR(2000) = '' --Comma separated List of columns to exclude from comparison list columns

DECLARE @TableName VARCHAR(256) = ''

SELECT @TableName = REVERSE(SUBSTRING(REVERSE(@Table1_FullName), 0, CHARINDEX('.', REVERSE(@Table1_FullName) + '.')))

--SELECT @TableName

DECLARE @SchemaName VARCHAR(256) = REVERSE(STUFF(REVERSE(@Table1_FullName), 1, LEN(@TableName) + 1, ''))

SELECT @SchemaName = REVERSE(SUBSTRING(REVERSE(@SchemaName), 0, CHARINDEX('.', REVERSE(@SchemaName) + '.')))

--SELECT @SchemaName

DECLARE @tmpExcludeColumns TABLE

(

Val VARCHAR(MAX)

)

;WITH CTE AS

(

SELECT CAST(LEFT(@ColListToExclude, CHARINDEX(',', @ColListToExclude + ',') - 1) AS VARCHAR(MAX)) AS Val

,CAST(STUFF(@ColListToExclude, 1, CHARINDEX(',', @ColListToExclude + ','), '') AS VARCHAR(MAX)) AS RecVal

UNION ALL

SELECT CAST(LEFT(RecVal, CHARINDEX(',', RecVal + ',') - 1) AS VARCHAR(MAX)) AS Val

,CAST(STUFF(RecVal, 1, CHARINDEX(',', RecVal + ','), '') AS VARCHAR(MAX)) AS RecVal FROM CTE

WHERE RecVal > ''

)

INSERT INTO @tmpExcludeColumns

SELECT Val FROM CTE

OPTION(MAXRECURSION 32767) --MAX RECURSION

DECLARE @JoinClause VARCHAR(4000) = ''

SELECT @JoinClause = @JoinClause + ' AND tbl1.' + COALESCE(SC.NAME, '') + ' = tbl2.' + COALESCE(SC.NAME, '') + CHAR(13)

FROM SYS.columns SC

INNER JOIN SYS.tables ST ON SC.object_id = ST.object_id

INNER JOIN SYS.schemas SCH ON SCH.schema_id = ST.schema_id

WHERE ST.NAME = @TableName AND SCH.name = @SchemaName

AND ((@IsIdentityToCompare = 1) OR (@IsIdentityToCompare = 0 AND SC.is_identity = 0))

AND SC.name NOT IN (SELECT Val FROM @tmpExcludeColumns)

ORDER BY SC.NAME

SELECT @JoinClause = STUFF(@JoinClause, 1, LEN(' AND '), '')

--SELECT @JoinClause

DECLARE @WhereClause VARCHAR(4000) = ''

SELECT @WhereClause = @WhereClause + --' OR tbl2.' + COALESCE(SC.NAME, '') + ' IS NULL ' + CHAR(13)

' OR tbl1.' + COALESCE(SC.NAME, '') + ' IS NULL OR tbl2.' + COALESCE(SC.NAME, '') + ' IS NULL ' + CHAR(13)

FROM SYS.columns SC

INNER JOIN SYS.tables ST ON SC.object_id = ST.object_id

INNER JOIN SYS.schemas SCH ON SCH.schema_id = ST.schema_id

WHERE ST.NAME = @TableName AND SCH.name = @SchemaName

AND ((@IsIdentityToCompare = 1) OR (@IsIdentityToCompare = 0 AND SC.is_identity = 0))

AND SC.name NOT IN (SELECT Val FROM @tmpExcludeColumns)

ORDER BY SC.NAME

--PRINT @WhereClause

SELECT @WhereClause = ' WHERE ' + STUFF(@WhereClause, 1, LEN(' AND '), '')

DECLARE @ColToDisplayStmt VARCHAR(2000) = ''

SELECT @ColToDisplayStmt = @ColToDisplayStmt + ', tbl1.' + + COALESCE(SC.NAME, '') + ', tbl2.' + COALESCE(SC.NAME, '') + CHAR(13)

FROM SYS.columns SC

INNER JOIN SYS.tables ST ON SC.object_id = ST.object_id

INNER JOIN SYS.schemas SCH ON SCH.schema_id = ST.schema_id

WHERE ST.NAME = @TableName AND SCH.name = @SchemaName

AND ((@IsIdentityToCompare = 1) OR (@IsIdentityToCompare = 0 AND SC.is_identity = 0))

AND SC.name NOT IN (SELECT Val FROM @tmpExcludeColumns)

--ORDER BY SC.NAME

SELECT @ColToDisplayStmt = STUFF(@ColToDisplayStmt, 1, LEN(', '), '')

DECLARE @SQLStatement VARCHAR(8000) = ''

SET @SQLStatement = 'SELECT ISNULL(tbl1.TableName, tbl2.TableName) AS TableName, ISNULL(tbl1.RowSEQ, tbl2.RowSEQ) AS RowSEQ, ' + @ColToDisplayStmt +

' FROM (SELECT ''Table 1 '' AS TableName, ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS RowSEQ, * FROM ' + @Table1_FullName + ') AS tbl1

FULL JOIN (SELECT ''Table 2 '' AS TableName, ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS RowSEQ, * FROM '

+ @Table2_FullName + ') AS tbl2 ON ' + @JoinClause + @WhereClause

+ ' ORDER BY RowSEQ, TableName, ' + @ColToDisplayStmt

PRINT @SQLStatement

EXEC (@SQLStatement)

Description

I have created this script to minimize the effort to compare two tables with same structure from different servers, databases, schema. For example if you need to compare employee table data from Quality server to Production server, you can use this script and customize as per your need.

Now to compare both tables from different servers also, add server name first then database, schema and table names. In this script i took first table to list all the columns from sys.columns.

First set the values in variables with full namespace in @Table1_FullName and @Table2_FullName. Now if you need to exclude identity comparison or not, you can set here in @IsIdentityToCompare. In case you want to exclude some columns during data comparison, you can set these column names in this variable with comma separated in this variable @ColListToExclude.

Conclusion

I have created this script to compare a bunch of tables dynamically from QA to Prod server and now sharing with you all. Please do rate, like, share this post if you really like my effort. Do share your comments also.

As I promised above, below is the download link of .sql file.

CompareTableData

Rate This
[Total: 0    Average: 0/5]


Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse,  Big Data and Cloud Solutions.
I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have good experience in windows and web application development using ASP.Net and C#.


Leave a Reply to Igor Cancel reply

Your email address will not be published. Required fields are marked *

*

2 thoughts on “Compare tables data on different databases with same structure

  • Igor

    Hi there,
    What about using of EXCEPT and INTERSECT t-sql command for this (msdn.microsoft.com/…/ms188055.aspx?ppud=4)?

    Example:

    — 1st step
    select from server1.db1.dbo.table1
    except
    select from server2.db2.dbo.table2
    go

    — 2nd step (vice versa to 1st step)
    select from server2.db2.dbo.table2
    except
    select from server1.db1.dbo.table1
    go

    Just idea, nothing earth shattering.
    GL

    • Gopal Krishna Ranjan
      Gopal Krishna Ranjan

      Hi Igor,
      I personally welcome your comment / suggestion and a heart-full thanks for giving your valuable time.
      Above what i shared as a post is just a way to achieve the goal of comprising two tables and there can be much more methods too. What you have suggested is also a solution and a good suggestion too.

      Thanks! keep posting your valuable comments.