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

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.

SELECT DB_NAME(ius.[database_id]) AS [Database],

OBJECT_NAME(ius.[object_id], ius.[database_id]) AS [TableName],

(ius.[last_user_lookup]) AS [last_user_lookup],

(ius.[last_user_scan]) AS [last_user_scan],

(ius.[last_user_seek]) AS [last_user_seek],

(ius.user_seeks) AS [last_user_seek],

(ius.user_scans) AS [last_user_scan],

(ius.user_lookups) AS [last_user_lookup],

(ius.user_updates) AS [last_user_updates],

ius.*

FROM sys.dm_db_index_usage_stats AS ius

WHERE ius.[database_id] = DB_ID()

order by tablename, last_user_lookup desc,last_user_scan  desc,last_user_seek  desc

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.