Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm wondering if there's a way in SQL Server to make a list of all tables and when they were last accessed (select / update / insert or delete) either by a user, view or stored procedure?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Jon gives you some links to SQL Server audit information in the comments. That is a definite way to see who is doing what and when on a server. Depending on what you are looking for that may be a good enough answer. Auditing isn't free, though. Capturing some of the data required can be expensive to trak and take some time to track.

One approach here - depending on what you are looking to do with this information is to look at the DMV - sys.dm.db_index_usage_stats this DMV shows you various statistics about access (insert, update, delete, scan, seek and lookups) for your indexes. You can join this DMV to tables that contain information about your tables or views like sys.tables or sys.indexes to see the details.

There are some caveats about this approach (data gets reset when SQL is restarted, there is a bug in SQL 2012 where the stats are apparently reset after an index rebuild, and other caveats described by Joe Scan in a couple posts) You can see an example of querying this data in this post from 2007 written for SQL 2005 but still most applies today.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.