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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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 -
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.