How can I check what tables have been updated?

[robWhitz]

I need to find out a couple of things on a daily basis,
mainly for backup reasons:-
- How can I determine which are the tables that are
updated on the current day?
- If such table exists, which database does it belong to?

Is there anywhere I can automate this, so that it will
show me a list of all tables updated for this day?

Would appreciate any help and advice on this matter.

Thanks in advance,
robwhitz

 

[Ravi29]

hi!
For your problem can be resolved by two ways
1. Keep on store the sql profile information on a particular day as your wish. Then you take the query from this file for your requirement.
2. It is posible from logfile. But you can't read normaily instead of using other thirdparty tools

bye
Ravi

 

[robWhitz]

Hi,

The tables are actually updated via a third-party application which I do not have much control over it. That's why I need to find out exactly what tables are being updated by the apllication.

For your 2 options, how can I go about setting it up?

Thanks,
Audrey

 

[tlbroadbent]

SQL Server does not automatically maintain an audit trail of changes. It is possible to read the transaction log. This is best done with a product such as Lumigent's Log Explorer. You can attempt to read the log with the undocumented DBCC LOG command. See the following link for details.

http://www.databasejournal.com/features/mssql/article.php/1442251#part_2_8

Ideally, the tables should have a datetime column for last update. If each table had such a column, you could search for updates in the last day. Of course that will only give you the current value not the previous value.

Many people add triggers to tables to capture before and after information when tables are updated. However, this could add considerable load on the server.

Another method of tracking activity is to create a trace and capture SQL update activity to a file or table.

You can also purcahse 3rd party software that will track updates and creat an audit trail of updates. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[awarnica]

Why don't you add a boolean field to the tables you need to track (we'll call 'Updated' ).

When the 3rd party vendor updates the table, set 'Updated' to true, after it is backed up set it back to false.

Then just search for the records where 'Updated' is set to true.