During the TechEd India 2013 presentations I received a question how to identify when any table is accessed by any of the user. It seems people would like to know if the table was used in any part of query by any user. The best possible solution is to create database audit task and watch the database table access. However, sometime we all want shortcut even thought it is not accurate. Here is how you can use DMV to do so. However, please note that this DMV will get reset when database services or servers are restart. Let me know if you think I should modify this DMV and have some better alternatives.
SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
AND ius.[object_id] = OBJECT_ID('YourTableName')
GROUP BY ius.[database_id], ius.[object_id];
Remember to change your database context to your current database as well make sure that you insert your table name in the object_id condition.
DMV sys.dm_db_index_usage_stats has columns related to last user lookup, last user scan and last user seek. Any table which is accessed will either go for seek or scan. We can watch these columns and figure out when the table was used last. Which ever value among the last_user_lookup, last_user_scan and last_user_seek is latest is the last user access of the table.
Reference: Pinal Dave (http://blog.sqlauthority.com)
I am using sql server 2005 & its need to change query
1) used OBJECT_NAME(ius.[object_id]) instead of
OBJECT_NAME(ius.[object_id], ius.[database_id]) to avoid error
2) used in where clause OBJECT_NAME(ius.[object_id]) NOT LIKE ‘sys%’ to avoid sys tables
& now its working fine
but can we differentiate when table accessed and when table updated etc…
SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE OBJECT_NAME(ius.[object_id]) NOT LIKE ‘sys%’
and ius.[database_id] = DB_ID(‘YourDatabaseName’)
–AND ius.[object_id] = OBJECT_ID(‘YourTableName’)
GROUP BY ius.[database_id], ius.[object_id];
Changed the original blog post to accommodate your suggestion so it will work with 2005 and onwards version.
i used u r Query but i am not getting last_user_lookup??