How to find out which column value changed in SQL, Sql Server

Language: Layout:

CREATE TABLE Yourtable1
    ([name] varchar(5), [ID] int, [Dept] varchar(3), [Email] varchar(7), [EmpID] int, [Salary] varchar(4), [Gender] varchar(1))
;
    
INSERT INTO Yourtable1
    ([name], [ID], [Dept], [Email], [EmpID], [Salary], [Gender])
VALUES
    ('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')
;

CREATE TABLE Yourtable2
    ([name] varchar(5), [ID] int, [Dept] varchar(3), [Email] varchar(9), [EmpID] int, [Salary] varchar(4), [Gender] varchar(1))
;
    
INSERT INTO Yourtable2
    ([name], [ID], [Dept], [Email], [EmpID], [Salary], [Gender])
VALUES
    ('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')
    
;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, '')

View schema
Execution time: 0,03 sec, rows selected: 4, rows affected: 12, absolute service time: 0,2 sec