Thursday, March 21, 2013

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


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.

If you are searching life partner. your searching end with now offer free matrimonial website which offer free message, free chat, free view contact information. so register here : Free matrimonial website