Dinamically determines which columns changed in Change Tracking enabled tables since last version for a given table

change-tracking-get-changed-columns-list.sql

EXEC sp_executesql N'
	DECLARE @sqlstmt nvarchar(max) = (
	SELECT
		N''SELECT	UT.SYS_CHANGE_VERSION, UT.SYS_CHANGE_OPERATION, STRING_AGG(CAST(UT.COLUMN_NAME AS nvarchar(max)), N'''','''') AS CHANGED_COLUMNS_LIST ''
	+	N'' FROM (SELECT SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,''
	+	STRING_AGG(N''CHANGE_TRACKING_IS_COLUMN_IN_MASK('' + CAST(C1.column_id as nvarchar(max)) + N'', SYS_CHANGE_COLUMNS) AS ['' + C1.[name] + '']'', N'','')
	+	N'' FROM CHANGETABLE(CHANGES '' + @table_name + '', '' + CAST(@version as nvarchar) + '') AS CT) AS PT''
	+	N'' UNPIVOT (COLUMN_CHANGED FOR COLUMN_NAME IN (''
	+	STRING_AGG(CAST(C1.[name] as nvarchar(max)), N'','')
	+	N'')) AS UT WHERE UT.COLUMN_CHANGED = 1 GROUP BY UT.SYS_CHANGE_VERSION, UT.SYS_CHANGE_OPERATION'' AS sqlstmt
	FROM	sys.columns AS C1
	WHERE	C1.[object_id] = OBJECT_ID(@table_name)
	)
	EXEC (@sqlstmt)
	'
,	@params = N'@table_name sysname, @version bigint'
,	@table_name = N'dbo.Companies'
,	@version = 1

change-tracking-get-changed-columns.sql

EXEC sp_executesql N'
	DECLARE @sqlstmt nvarchar(max) = (
	SELECT
		N''SELECT	UT.SYS_CHANGE_VERSION, UT.SYS_CHANGE_OPERATION, UT.COLUMN_NAME''
	+	N'' FROM (SELECT SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,''
	+	STRING_AGG(N''CHANGE_TRACKING_IS_COLUMN_IN_MASK('' + CAST(C1.column_id as nvarchar(max)) + N'', SYS_CHANGE_COLUMNS) AS ['' + C1.[name] + '']'', N'','')
	+	N'' FROM CHANGETABLE(CHANGES '' + @table_name + '', '' + CAST(@version as nvarchar) + '') AS CT) AS PT''
	+	N'' UNPIVOT (COLUMN_CHANGED FOR COLUMN_NAME IN (''
	+	STRING_AGG(CAST(C1.[name] as nvarchar(max)), N'','')
	+	N'')) AS UT WHERE UT.COLUMN_CHANGED = 1'' AS sqlstmt
	FROM	sys.columns AS C1
	WHERE	C1.[object_id] = OBJECT_ID(@table_name)
	)
	EXEC (@sqlstmt)
	'
,	@params = N'@table_name sysname, @version bigint'
,	@table_name = N'dbo.Companies'
,	@version = 1