SQL SERVER – Identify Last User Access of Table using T-SQL Script

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];

Solarwinds

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)

Solarwinds
,
Previous Post
SQLAuthority News – How to Avoid Procrastination – Professional Development #001 – Video
Next Post
SQL SERVER – Fix – Error: 1060 The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer

Related Posts

25 Comments. Leave new

  • Sanjay Monpara
    March 21, 2013 3:19 pm

    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];

    Reply
  • 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?

    Reply
  • i used u r Query but i am not getting last_user_lookup??

    Reply
  • Dietrich Herrmann
    September 23, 2013 9:01 pm

    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

    Reply
  • rudy.maringer@widy-conseils.com
    October 9, 2013 2:41 pm

    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.

    Reply
  • 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?

    Reply
  • Hi Pinal,

    How to find “who has accessed the table last time” ?

    Reply
  • Thank you! Really made a horrible inventory project so much easier!

    Reply
  • 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.

    Reply
  • Very useful, thank you!

    Reply
  • 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.

    Reply
  • 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

    Reply
  • how to check it for sql server 2000

    Reply
  • Hitesh Prajapati
    May 18, 2017 4:14 pm

    Hi Pinal

    Please help me on this this is high priority

    how to check it for sql server 2000 ?

    Thanks

    Reply
  • How to find the last access time of index in SQL server

    Reply
  • 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.

    Reply
  • hi. can we find when a particular user accessed a table.

    Reply
  • Hi,

    Can you help me find Users who accessed a particular Database this month.

    Reply

Leave a Reply

Menu