CODEWITHSUNDEEP
sql-server-2008
user1308182
user1308182

Reputation: 41

Using CHANGETABLE() on all tables

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.

Upvotes: 1

Views: 5879

Answers (3)

Gavin Cox
Gavin Cox

Reputation: 11

Similar to @Pondlife but without the cursor

   DECLARE @SQL NVARCHAR(MAX) = N''
   DECLARE @Version INT = 60

    ;WITH TrackedTables AS
    (
        SELECT TableName = CONCAT('[',s.[name],'].[', o.[name],']')
        FROM sys.change_tracking_tables t 
            JOIN sys.objects o on o.object_id = t.object_id
            JOIN sys.schemas s ON o.schema_id = s.schema_id
    )
    SELECT @SQL = @SQL + CONCAT('SELECT ',''''+TableName+'''',', Recordcount = COUNT(*), MinPK = MIN(ID), MaxPK=MAX(ID), MinVersion=MIN(SYS_CHANGE_VERSION), MaxVersion = MAX(SYS_CHANGE_VERSION)',' FROM CHANGETABLE(CHANGES ',TableName,',', @Version,') c UNION ALL ')
    FROM TrackedTables 

    SET @SQL = LEFT(@SQL,LEN(@SQL)-10)
    EXEC sp_ExecuteSQL @SQL

Upvotes: 1

Sean Summers
Sean Summers

Reputation: 2684

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.

Upvotes: 0

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 0

Related Questions