last read/write time of table

Is there a way to get the last read/write time of a table?

I want to have a few tables, but only allow them to exist if they have been used in the last 30 days. I want to set up a "purge" job to clear out any tables that have not been used in 30 days.

I think that unless you were to add a datetime column to each table, that information will be difficult to obtain.

For you purposes, it may be useful to add a datetime column, DEFAULT getdate(). If the application never provides a value, the server will always provide the current date/time on entry. UPDATEs would require a trigger.

You 'could' add a trigger to each table that updates a single field in a maintenance table. -Probably not too good of an idea though...

|||In addition to Arnies response and for the sake that you really need to know the last modification date, you could use a log reader to determine the last modification date. SQL Server does not provide an easy way to read the logs, therefore you would probably need to use a thrid party component.

Jens K. Suessmeyer.

http://www.sqlserver2005.de