Compare tables data on different databases with same structure

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