How can a Table's Last Modified date be returned in SQL Server 2005?

Question

How can a Table's Last Modified date be returned in SQL Server 2005?

I did see one on the Table Properties page. There is a Created Date but no Modified date.

If it is not available, what would be some other ways to add this functionality?

Here are a few that come to mind:

1. Add another column, to the table, that a trigger would update whenever the record was added or changed. The one con, to this approach, is Deletes would not be tracked.
2. Add another table (TableModifiedDate) that would contain a Table Name and Modified Date. Then add a trigger to the tables that you want to track which will update TableModifiedDate.

Answer 1

Paul Nielson's AutoAudit is a quick and effective way of retro-fitting this to a database. He has a demo screencast here.

Answer 2

This Blog entry contains information on how to do it on SQL Server 2008 and 2005.

• On 2008: using the new Server Auditing feature
• On 2005: using Dynamic Management Views (DMV)

SQL Server 2000 has no built-in possibility to do it, so you'll need a workaround, like you already mentioned.