How to determine the last time a table was accessed ?

I'm trying to do some housekeeping. I want to delete user tables from database(s) that have not had any activity...

I cannot seem to find a mechanism for accomplishing this. sysobjects only shows the createdate, not the last time a user table had a SELECT, INSERT, UPDATE or DELETE operation performed on it.

Anyone know how to do this ?

Thanks

(P.S. this is the second posting of this question today, as I went to my threads and I do not see the original post - sorry for the duplicate, but as I say, I do not see the original so am re-posting).

randyvol

SQL server doesn't store such information and all those process is logged in transction log, and you might need third party tools in this case to audit the events or run server side trace if you want to schedule such information for time being.|||

Satya -

First and foremost thank you for your reply.

Next - what?!!? WOW! I just naturally assumed that SQL Server would do this. I cannot imagine a product that is being touted as 'ready for prime time' does not provide such basic necessities. Don't get me wrong, I really like the product, especially the 2k5 instantiation, which is why I'm even more perplexed.

How is one supposed to know over time what tables one can delete with absolute safety? I understand that 3rd party tools provide this ability, but surely they leverage something (perhaps undocumented) in the basic system? Teradata, for instance provides this information - I know, I'm a certified Teradata Master and have used that system's entries on many occasions to ascertain whether or not a table was really 'stale' and could be dropped to free up disk. I would not think it is that big a deal (or that much overhead) to have one extra column, say in sysobjects, for instance, 'last updated'.

I just cannot believe MSFT overlooked this, or expects me to cough up dollars for a 3rd party tool to do this routine maintenance chore. This is something I'd expect to find in the sys tables for sure. Doesn't have to be elegant and exposed into Studio - just basic data I can fetch with a query would suffice.

As for the tranlog.. it is transient. I'm sure that there is data there to mine, but it doesn't help me on the 100's of tables already existent on our legacy system, that have been around for years.

I sure hope MSFT decides to provide this ability soon.

(It does explain why I cannot find any documentation on how to do this though ;-)

Oh well, I guess I'll have to go build my own stuff and let it cook for a couple of quarters to see if tables are stale or not.

Regards

randyvol