October 5, 2022 at 7:29 pm
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 6, 2022 at 1:01 pm
----------------------------------------------------
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
October 6, 2022 at 1:16 pm
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
October 6, 2022 at 2:49 pm
Thank you both for the replies. Ratbak, this is exactly the query I was looking for.
I greatly appreciate your time
October 7, 2022 at 11:54 am
This was removed by the editor as SPAM
October 11, 2022 at 11:37 pm
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_%'
October 11, 2022 at 11:42 pm
Or is this information already available in a system table and I am re-inventing?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply