Need to know the table name in changetable from change tracking option

1

Dear Team,

I am using the change tracking option in the sql server 2016. This feature provide the primary key of affected record's in the watching table , I want to know the name of the watching table also.

For Example

Script
Script923×196 14.6 KB

from above image, it provides the primary key of affected rows of the watching table,

I want to know table name also

If it possible, could you provide the solution for me ?

2

The table name is 'Person.Address'; you specify it in the CHANGETABLE function.

If you want to see it in the query, add it there:


SELECT 'Person.Address' AS table_name, *
...
3

Is there any option to watch the multiple table at a time in changeable function ?
If it possible, could you provide the solution for me ?

4 
DECLARE @sql nvarchar(max)=N'';
SELECT @sql+=CONCAT(N'SELECT ', QUOTENAME(s.name + N'.' + t.name, '''')
,N' AS table_name, * FROM CHANGETABLE(CHANGES '
,QUOTENAME(s.name), N'.', QUOTENAME(t.name),N' ,1) as change_table;') 
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
INNER JOIN sys.change_tracking_tables c ON t.object_id=c.object_id
EXEC(@sql);

I will comment that I don't really understand the need for pulling changes for all tables in a single batch, unless every one of your tables has the exact same schema, storing the exact same kind of data...in which case, why are they separate tables?