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