Getting all changed tables and rows using Change Tracking in SQL Server

Question

I need to get all the tables that were changed in a database, including the rows' IDs that were updated/added/removed.

So, if I have table table1 and table2, and rows with ID 15 and 16 were changed in table1, and rows with IDs 200 and 201 were changed in table2, I want to run a script that returns:

+-----------+-------+
| tableName | rowId |
+-----------+-------+
| table1    |    15 |
| table1    |    16 |
| table2    |   200 |
| table2    |   201 |
+-----------+-------+

I managed to find this script:

set nocount on;

-- We want to check for changes since the previous version
--declare @prevTrackingVersion int = INSERT_YOUR_PREV_VERSION_HERE
-- Comment out this line if you know the previous version
declare @prevTrackingVersion int = CHANGE_TRACKING_CURRENT_VERSION() - 1

-- Get a list of table with change tracking enabled
declare @trackedTables as table (name nvarchar(1000));
insert into @trackedTables (name)
select sys.tables.name from sys.change_tracking_tables 
join sys.tables ON tables.object_id = change_tracking_tables.object_id

-- This will be the list of tables with changes
declare @changedTables as table (name nvarchar(1000));

-- For each table name in tracked tables
declare @tableName nvarchar(1000)
while exists(select top 1 * from @trackedTables)
begin
  -- Set the current table name
  set @tableName = (select top 1 name from @trackedTables order by name asc);
  print @tableName

  -- Determine if the table has changed since the previous version
  declare @sql nvarchar(250)
  declare @retVal int 
  set @sql = 'select @retVal = count(*) from changetable(changes ' + @tableName + ', ' + cast(@prevTrackingVersion as varchar) + ') as changedTable'
  exec sp_executesql @sql, N'@retVal int output', @retVal output 
  print @retVal
  if @retval > 0
  begin
    insert into @changedTables (name) select @tableName
  end

  -- Delete the current table name 
  delete from @trackedTables where name = @tableName;  
end

select * from @changedTables;

However, it only outputs the names of the tables that were modified.

So, I know that this command:

   SELECT * FROM CHANGETABLE(CHANGES table1, 0) as CT;

outputs this:

+----------------------+----+
| SYS_CHANGE_OPERATION | ID |
+----------------------+----+
| I                    | 15 |
| I                    | 16 |
+----------------------+----+

In the script, this same command is used:

  set @sql = 'select @retVal = count(*) from changetable(changes ' + @tableName + ', ' + cast(@prevTrackingVersion as varchar) + ') as changedTable'

but it only gets the count, and then checks if it is greater than 0 (if there is any modifications for this table):

  if @retval > 0
  begin
    insert into @changedTables (name) select @tableName
  end

However, as mentioned, I need to not only get the tables modified, but the IDs of the rows that were modified.

I feel like I need to JOIN, but I'm not really sure how to work this out.

Thanks

Answer 1

You could create a trigger on each table in your schema (or rather the one you truly need) like this

CREATE TRIGGER TRX_TableName_ChangeLog ON  TableName
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    DECLARE @TABLE_NAME NVARCHAR(400)
    SELECT @TABLE_NAME = OBJECT_NAME(parent_object_id)
    FROM sys.objects 
    WHERE sys.objects.object_id = @@PROCID

    INSERT INTO ChangeTableLog (TABLE_NAME, TABLE_ID)
    SELECT @TABLE_NAME,INSERTED.ID
    FROM INSERTED --or delete
END

The creation of the trigger could be dynamically something like this:

DECLARE @TABLE_NAME nvarchar(200),@Trigger nvarchar(max)

DECLARE CURS CURSOR LOCAL FAST_FORWARD FOR
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN CURS
FETCH NEXT FROM CURS INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Trigger =
    'CREATE TRIGGER TRX_'+@TABLE_NAME+'_ChangeLog 
       ON '+@TABLE_NAME+'
       AFTER INSERT,DELETE,UPDATE
    AS 
    BEGIN
        DECLARE @TABLE_NAME NVARCHAR(400)

        SELECT @TABLE_NAME = OBJECT_NAME(parent_object_id)
        FROM sys.objects 
        WHERE sys.objects.object_id = @@PROCID
    
        INSERT INTO ChangeTableLog (TABLE_NAME, TABLE_ID)
        SELECT @TABLE_NAME,INSERTED.ID
        FROM INSERTED
    END'

    EXEC(@Trigger)
    FETCH NEXT FROM CURS INTO @TABLE_NAME
END
CLOSE CURS
DEALLOCATE CURS

Personally, I dislike the use of triggers but there is no doubt they are easy to implement. I don't know the scale of your system, but the best scenario is that the service which makes the entries will also include a new column in each table to keep changes or just state the date of execution per entry.

Answer 2

What you need to do is -

1. Find out all the tracking tables and their primary keys. The result returned from CHANGETABLE includes the primary key of the tracking table, NOT ROWID as you thought. For example, if table t1's PK is defined on t1.id, then CHANGETABLE for t1 will return "id" column; if table t2's PK is defined on (id1, id2), then CHANGETABLE for t2 will return "id1" and "id2".

Try to run CHANGETABLE for one table can help you understand what I am saying -

select *
  from CHANGETABLE(CHANGES your_table, 0 /*track version*/) chg

To keep things simple, I assume all your tracking tables have single-column PK. You can find out tracking tables and their PKs by -

select c.table_name
      ,k.column_name pk
  from sys.change_tracking_tables t
      ,information_schema.table_constraints c
      ,information_schema.key_column_usage  k
 where object_name(t.object_id) = c.table_name
   and c.constraint_type = 'PRIMARY KEY'
   and c.table_name = k.table_name
   and c.constraint_name = k.constraint_name

2. Run CHANGETABLE for each tracking table and combine the results.

Here is the whole modified script -

set nocount on;

-- We want to check for changes since the previous version
--declare @prevTrackingVersion int = INSERT_YOUR_PREV_VERSION_HERE
-- Comment out this line if you know the previous version
declare @prevTrackingVersion int CHANGE_TRACKING_CURRENT_VERSION() - 1

-- Get a list of table with change tracking enabled
declare @trackedTables as table (table_name nvarchar(100), pk nvarchar(100));
insert into @trackedTables (table_name, pk)
select c.table_name
      ,k.column_name pk
  from sys.change_tracking_tables t
      ,information_schema.table_constraints c
      ,information_schema.key_column_usage  k
 where object_name(t.object_id) = c.table_name
   and c.constraint_type = 'PRIMARY KEY'
   and c.table_name = k.table_name
   and c.constraint_name = k.constraint_name

-- This will be the list of changes
declare @changes as table (table_name varchar(100), pk varchar(100))

-- For each table name in tracked tables
declare @table_name nvarchar(100)
       ,@pk nvarchar(100)
while exists(select top 1 * from @trackedTables)
begin
  -- Set the current table name
  select top 1 @table_name = table_name, @pk = pk from @trackedTables order by table_name asc;

  insert into @changes (table_name, pk)
  exec ('select ''' + @table_name + ''', ' + @pk + ' from CHANGETABLE(CHANGES ' + @table_name + ', ' + @prevTrackingVersion + ') chg')

  -- Delete the current table name 
  delete from @trackedTables where table_name = @table_name;  
end

select * from @changes;