Is there metadata in SQL Server to determine the date/time of the last update?

Question

Does SQL Server 2005 maintain built-in, queryable, row-level last-modified timestamp metadata?

I'm doing some analysis on a database that did not include any sort of column to track a revision date/time stamp. I usually create columns for this purpose, but the designer of this database didn't, so I'm stuck with reconstructing history on his data.

Answer 1

No, You have to build it into your table yourself.

many people add some sort of LastChangeDate and LastChangeUserID column on each table to track these on.

If it isn't there, you're at the mercy of whatever data you can pull from related tables, if at all.

Answer 2

Without the presence of explicit change tracking, your only real option will be to review database backups and the transaction log. You can get some information with the DBCC log command.

Answer 3

Nothing automatic.

I like to use AutoAudit - it's pretty painless to apply it to tables and works well. It will even create views for deleted rows and a DDL trigger to automatically update the triggers if a table schema changes.