Find data difference between two schema identical tables

Declare @vSQL varchar(max)

Declare @vCols varchar(max)

Create Table vTable1 (id int, StudentID int, Dept varchar(10),BookID int)

Create Table vTable2 (id int, StudentID int, Dept varchar(10),BookID int)

Insert into vTable1

Select 1,123,'CS',465 Union All

Select 2,123,'CS',345 Union All

Select 3,223,'TE',190

Insert into vTable2

Select 1,123,'CS',465 Union All

Select 2,223,'TE',345 Union All

Select 3,223,'TE',190

-- Get the column names from schema with case statements to get 0 or 1 as
result

-- Now, this will depend upon the columns of your actual tables

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

print @vCols

-- Concatenate the @vCols with main sql

Set @vSQL = ' Select a.id,' + @vCols + ' From vTable1 a

Inner Join vTable2 b on b.ID = a.ID '

Print @vSQL

Exec (@vSQL)

 

Drop table vTable1

Drop table vTable2