Using CHANGETABLE() on all tables

Question

I need to know which are the tables changed in the database using change tracking. Is there any table where i can find the last updated tables with the commit id? I can use select * from CHANGETABLE(CHANGES taitemnames,25262)ct order by sys_change_version desc but this requires me to run it once for every table to check for changes.

Answer 1

I'm not familiar with this feature, but if your issue is how to query multiple tables using CHANGETABLE() then I assume you could use a stored procedure to loop over all table names and run the query using dynamic SQL:

declare 
    @sql nvarchar(max), 
    @parameters nvarchar(max), 
    @TableName nvarchar(128), 
    @Version bigint

set @Version = CHANGE_TRACKING_CURRENT_VERSION()

declare Tables cursor local fast_forward
for 
select name from sys.tables where... -- add conditions here if necessary

open Tables
fetch next from Tables into @TableName
while @@fetch_status = 0
begin
    set @sql = N'select * from CHANGETABLE(CHANGES ' + quotename(@TableName) + ', @LastVersion)ct order by sys_change_version desc'
    set @parameters = N'@LastVersion bigint'
    exec sp_executesql @sql, @parameters, @LastVersion = @Version
    fetch next from Tables into @TableName
end

close Tables
deallocate Tables

You could combine this with an INSERT in the dynamic SQL to write the results into a table that you then query for reporting and analysis.

Answer 2

Try sys.CHANGE_TRACKING_TABLES (documented on MSDN here).

You'll have to use OBJECT_NAME to get the table name from the first column.