Change tracking - Date a row changed

Question

Change tracking - Date a row changed

cbarus

SSCommitted

Points: 1906
More actions
October 5, 2022 at 7:29 pm

#4095967

We have change tracking enabled on one of our databases and I am looking for a query where I could get the timestamp/date on when a row changed. Any help is greatly appreciated. Thanks a lot.
October 7, 2022 at 11:54 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397956 More actions · Answer 1

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software

ratbak Ten Centuries Points: 1102 More actions · Answer 2

https://stackoverflow.com/questions/13821161/find-change-tracking-time-in-sql-server

select tc.commit_time, ....
from
changetable(changes <table>, 0) c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts

cbarus SSCommitted Points: 1906 More actions · Answer 3

Thank you both for the replies. Ratbak, this is exactly the query I was looking for.

I greatly appreciate your time

cbarus SSCommitted Points: 1906 More actions · Answer 4

Here is what I am trying to do and my query below. I am trying to see how many rows changed per table in a day and I would insert them into a table to keep history. I get an error.

declare @sitename varchar(100)

set @sitename = 'Denver'

select @sitename,convert(varchar, commit_time, 10),o.name, count(*), convert(varchar, getdate(), 10)

from changetable(changes o.name, 0) c

join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts

inner join sys.all_objects o on c.object_id = o.object_id where name not like 'report_%'

group by convert(varchar, commit_time, 10) having convert(varchar, getdate(), 10) = convert(varchar, commit_time, 10)

SQL Server does not like o.name.

========================================================================================

The other option I have is to generate dynamic SQL and execute each of those statements and it works just fine. What am I doing wrong? Any help is greatly appreciated. The dynamic SQL is:

declare @sitename varchar(100)

set @sitename = 'Denver'

select 'insert into dbo.ChangeRowCount (SiteName, ChangeDate, TableName, ChangeRowCount, LastInsertedDate) select '''+@sitename+''', convert(varchar, commit_time, 10),'''+o.name+''', count(*), convert(varchar, getdate(), 10)

from

changetable(changes '+o.name+', 0) c

join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts

group by convert(varchar, commit_time, 10) having convert(varchar, getdate(), 10) = convert(varchar, commit_time, 10)'

from sys.change_tracking_tables c

inner join sys.all_objects o on c.object_id = o.object_id where name not like 'report_%'

cbarus SSCommitted Points: 1906 More actions · Answer 5

Or is this information already available in a system table and I am re-inventing?