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?
Sql-server – way to list all tables and when they were last accessed
sql serversql-server-2008
Related Question
- SQL Server Security – Server Permissions for Activation Stored Procedure of a Server Broker Queue
- Sql-server – Database Last Usage
- SQL Server 2000 – How to GRANT Permissions to All Tables, Views, Procedures
- SQL Server – How to List All Tables from All User Databases
- SQL Server – Efficiently Check for NULL Values in All Columns for Multiple Tables
- SQL Server Replication: MSins, MSdel, and MSupd Procedures Creation
- SQL Server 2012 – Error Using OPENROWSET from a View as a Non-sysadmin
Best Answer
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_statsthis 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.