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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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 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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy