How to find out which column value changed in SQL

Question

I have two tables with 100 of columns, and I want to find out how which column value changed in those two tables. I can find which row has changed but I want to which column was changed not the whole row.

Table 1 
          name   ID    Dept   Email       EmpID    Salary  Gender  
          Rob     1     IT     I@i.com     100    5000       M 
          Mary    2     HR     M@m.com     20      6000      F 
          Jack    3     IT     J@j.com     30       7000     M 
          Harry   4     Fin     h@h.com    50       5000     M 
          Jay     5     Eng     Null        60       5000    M  
          Ken     6     HR      K@K.com     70       Null    M  

 Table 2 
          name   ID    Dept   Email       EmpID   Salary  Gender  
          Rob     1     IT     I@i.com     100    5000       M 
          Mary    2     HR     M@m.com     20      6000      F 
          Jack    3     IT     J@j.com     150     7000     M 
          Harry   4     Fin     h@h.com    50       Null    M 
          Jay     5     Eng     Jy@jy.com  60       5000     M  
          Ken     6     HR      K@K.com    70       6000     M  

As we can see Email for Jay existed, Emp ID for Jack was changed, Salary for Harry is null in Table 2 and Salary was added for Ken.

Expected Output (If this is possible, As I don't want to see all the row values, I just want to see which column value changed, as ID is a unique identifier, I would like to know which ID did column value change)

 ID     columnvaluechanged
  3        EmpID 
  4        Salary 
  5        Email 
  6        Salary 

Answer 1

Here is one way using unpivot technique

;WITH tab1
     AS (SELECT id,
                colName,
                value
         FROM   Yourtable1
                CROSS apply (VALUES (NAME,'name'),(Dept,'Dept'),(Email,'Email'),
                                    (Cast(EmpID AS VARCHAR(50)),'EmpID'),
                                    (Cast(Salary AS VARCHAR(50)),'Salary'),
                                    (Gender,'Gender')) cs (value, colName)),
     tab2
     AS (SELECT id,
                colName,
                value
         FROM   Yourtable2
                CROSS apply (VALUES (NAME,'name'),(Dept,'Dept'),(Email,'Email'),
                                    (Cast(EmpID AS VARCHAR(50)),'EmpID'),
                                    (Cast(Salary AS VARCHAR(50)),'Salary'),
                                    (Gender,'Gender')) cs (value, colName))
SELECT t1.ID,
       t1.colName,
       t1.value AS tab1_value,
       t2.value AS tab2_value
FROM   tab1 t1
       INNER JOIN tab2 t2
               ON t1.ID = t2.ID
                  AND t1.colName = t2.colName
                  AND Isnull(t1.value, '') <> Isnull(t2.value, '') 

• Demo