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 (https://blog.sqlauthority.com)
27 Comments. Leave new
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];
Not able to find which user has accessed.
but how can I ffind out which user, access to which table, what action (update/insert/delete) he/she has done and timestamp of it?
i used u r Query but i am not getting last_user_lookup??
Hi, it is a good query, but I want know in addition which user has made the changes of the table.
How to do this?
Regards-
Dietrich
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],
MAX(ius.[last_user_update]) AS [last_user_update]
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]
order by [last_user_update] desc;
Rudy.
Can we find out if any SSIS package is using the table or if any dataload or data extract have been performed on the table?
You can make use a profile to know this
Hi Pinal,
How to find “who has accessed the table last time” ?
Thank you! Really made a horrible inventory project so much easier!
Hi Pinal,
The server is restarted couple of times after a particular table was last accessed, is there a way to still check the last user access details? The DMV is no good is this case.
Very useful, thank you!
How can we know accessing time for a specific table not only last accessing time but i want to know each time when any one access the table. And it will be very good for me when i can know who and when access table.
HI Pinal,
How to identity Unused Procedures in my Current Database? Should not take from Proc cache. Pls share T-sql query to find out unused procs.
regards,
Patan
This is very risky as you may have some procedures which are used only Monthly.
how to check it for sql server 2000
Are you serious? Still using SQL 2000 for production?
Hi Pinal
Please help me on this this is high priority
how to check it for sql server 2000 ?
Thanks
How to find the last access time of index in SQL server
Hi Pinal,
This approach only work if the index has been used, secondly in newer sql editions the stat data are fushed when rebuilding index.
#Hitesh: SQL2000 is not supported, you have to migrate to something supported:-)
#Sheriff: You have to implement an audit as Pinal explains.
hi. can we find when a particular user accessed a table.
Hi,
Can you help me find Users who accessed a particular Database this month.
How can i find user who access the database in the last 24hrs on Azure Database
this is not listing all the tables in the database. if the table is very old never accessed, index usage stats dmv is not keeping entry in it.
what is the deference between table not listed in the output and table having all the field last_user_* as null.