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

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.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

12 thoughts on “SQL SERVER – Identify Last User Access of Table using T-SQL Script

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

  2. 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?

  3. 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

  4. 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.

  5. 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?

  6. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s