How to find recent sql update operations acting upon a certain table (SQL Server 2005)

Question

say I want to find the latest added rows (UPDATE by any user, not necessarily the one which is executing UPDATE) in XX table.

Answer 1

Please refer to SQL Docs & look for OUTPUT clause (that you can use with UPDATE/INSERT to get the affected records).

http://msdn.microsoft.com/en-us/library/ms177564.aspx

Answer 2

You would need to use a Transaction Log reader tool. There are several free ones available as well as commercial offerings.

• ApexSQL Log

You could also try this undocumented command:

 DBCC LOG(<database name>[,{0|1|2|3|4}]).

If you're using SQL Server 2000, RedGate have a free tool called SQL Log Rescue.

EDIT: Documentation for DBC LOG: (1) (2)

Answer 3

SQL Server Profiler will allow you to track hits to the database in real time. You can set filters on a number of properties to ge the output you need.