Search This Blog

Thursday, July 1, 2010

Data Difference of two identical Tables

This script was written to answer a question on
sqlservercentral.com. The original poster wanted to have a dynamic stored
procedure which can tell you the columns with different values in two tables.



Here is the code;


ALTER Procedure uspTableDiff
   @pTableName1 varchar(100), -- Name of First Table
   @pTableName2 varchar(100), -- Name of 2nd Table
   @pJoinCondition varchar(Max), -- Join Condition. I am assuming that
                                 -- you will use alias a for table in
                                 -- @pTableName1 and b for
                                 -- table in @pTableName2
   @pIDColumns varchar(Max) -- Valid ID columns to diplay
As
Begin
   Declare @vSQL varchar(max)
   Declare @vCols varchar(max)
   Set @vCols = ''
    -- Check if the specified tables areidentical or

   -- not as you will match schema wise
   -- identical tables.

   if Exists(
      Select *
      from
      (
      Select [name] from sys.columns
      where Object_id = Object_id(@pTableName1)
      ) c1
      Full Outer Join
      (
      Select [name]
      from sys.columns
      where Object_id = Object_id(@pTableName2)
     ) c2 on c2.[name] = c1.[name]
     Where c2.[name] is Null or c1.[Name] is Null
   )
   begin
      Select 'Tables Not Identical'
      Return
   end 


   Select @vCols =
      Stuff((Select ',case when IsNull(a.' + [name] +
                               ','''') = IsNull(b.' + [name] + ','''')
                       then 1 else 0 end
                       as [SIM_' +   @pTableName1 + '.' + [name] + ']'
   from sys.columns
   where Object_id = Object_id(@pTableName1) for XML Path('')),1,1,'')

   -- Concatenate the @vCols with main sql
   Set @vSQL = ' Select ' + @pIDColumns + ',' + @vCols
   + ' From '+ @pTableName1 +  ' a Inner Join '
   + @pTableName2 +
   ' b on ' + @pJoinCondition


   Print @vSQL
   Exec (@vSQL)
end

No comments:

Post a Comment