Dynamic Change Capture

DynamicChangeCapture.sql

USE Pantheon;
GO

DROP TABLE IF EXISTS #OutputTableDynamic;
GO
CREATE TABLE #OutputTableDynamic
     (
         GotAnyChangeID int         NOT NULL,
         OldColumn1     int         NOT NULL,
         NewColumn1     int         NOT NULL,
         OldColumn2     char(1)     NOT NULL,
         NewColumn2     char(1)     NOT NULL,
         OldColumn3     date        NOT NULL,
         NewColumn3     date        NOT NULL,
         OldColumn4     bigint      NULL,
         NewColumn4     bigint      NULL,
         OldColumn5     varchar(50) NOT NULL,
         NewColumn5     varchar(50) NOT NULL,
         OldColumn6     datetime    NULL,
         NewColumn6     datetime    NULL
     );

DECLARE @ChangeLogAsXml xml,
        @Id             int = 1;

UPDATE  gac
SET     gac.Column1 = 3,
        gac.Column2 = 'D',
        gac.Column6 = GETDATE()
OUTPUT inserted.GotAnyChangeID,
       Deleted.Column1, Inserted.Column1,
       Deleted.Column2, Inserted.Column2,
       Deleted.Column3, Inserted.Column3,
       Deleted.Column4, Inserted.Column4,
       Deleted.Column5, Inserted.Column5,
       Deleted.Column6, Inserted.Column6
INTO #OutputTableDynamic
FROM    dbo.GotAnyChange AS gac
WHERE   gac.GotAnyChangeID = @Id;

DECLARE @DynamicSql nvarchar(MAX),
        @NewColumns nvarchar(MAX),
        @OldColumns nvarchar(MAX),
        @columns    nvarchar(MAX);

SET @NewColumns =
  (
      SELECT    STUFF(
                    (
                        SELECT TOP (1024)
                                    ', CAST(ISNULL(' + QUOTENAME(RIGHT(tc.name, LEN(tc.name))) + ', '''') AS nvarchar(20)) AS '
                                    + QUOTENAME(RIGHT(tc.name, LEN(tc.name) - 3))
                        FROM        tempdb.sys.tables AS tt
                        INNER JOIN  tempdb.sys.columns AS tc
                            ON tt.object_id = tc.object_id
                        WHERE       tt.name LIKE '#OutputTableDynamic%'
                          AND       tc.name LIKE 'New%'
                        ORDER BY    tc.column_id
                        FOR XML PATH('')
                    ), 1, 2, ''));
SET @OldColumns =
  (
      SELECT    STUFF(
                    (
                        SELECT TOP (1024)
                                    ', CAST(ISNULL(' + QUOTENAME(RIGHT(tc.name, LEN(tc.name))) + ', '''') AS nvarchar(20)) AS '
                                    + QUOTENAME(RIGHT(tc.name, LEN(tc.name) - 3))
                        FROM        tempdb.sys.tables AS tt
                        INNER JOIN  tempdb.sys.columns AS tc
                            ON tt.object_id = tc.object_id
                        WHERE       tt.name LIKE '#OutputTableDynamic%'
                          AND       tc.name LIKE 'Old%'
                        ORDER BY    tc.column_id
                        FOR XML PATH('')
                    ), 1, 2, '' ) );
SET @columns =
  (SELECT   STUFF(
                (   SELECT TOP (1024)
                                ', ' + QUOTENAME(RIGHT(tc.name, LEN(tc.name) - 3))
                    FROM        tempdb.sys.tables AS tt
                    INNER JOIN  tempdb.sys.columns AS tc
                        ON tt.object_id = tc.object_id
                    WHERE       tt.name LIKE '#OutputTableDynamic%'
                      AND       tc.name LIKE 'New%'
                    ORDER BY    tc.column_id
                    FOR XML PATH('')
                ), 1, 2, ''));
SET @DynamicSql =
    N'SELECT New.ColumnName AS CN, New.NewValue AS NV, Old.OldValue AS OV FROM (SELECT Unpvt.GotAnyChangeId, Unpvt.ColumnName, Unpvt.NewValue FROM (SELECT [GotAnyChangeId], ' + @NewColumns
    + N' FROM #OutputTableDynamic ) AS DataSource UNPIVOT (NewValue FOR ColumnName IN (' + @columns
    + N') ) AS Unpvt ) AS New INNER JOIN ( SELECT Unpvt.GotAnyChangeId, Unpvt.ColumnName, Unpvt.OldValue FROM (SELECT [GotAnyChangeId], ' + @OldColumns
    + N' FROM #OutputTableDynamic ) AS DataSource UNPIVOT (OldValue FOR ColumnName IN (' + @columns
    + N')) AS Unpvt) AS Old ON Old.ColumnName = New.ColumnName AND Old.GotAnyChangeId = New.GotAnyChangeId WHERE New.NewValue != Old.OldValue FOR XML PATH(''Change''), ROOT(''Changes'')';

EXECUTE sp_executesql @DynamicSql;