Query to find when the table last accessed on SQL Server 2005 and above

 

------ Query to find when the User Table Last accessed ---------

WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
    MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

------ Query to find when the Database Last accessed ---------

SELECT name, last_access =(
                select X1= max(bb.xx)
                from ( select xx =
                        max(last_user_seek)
                        where max(last_user_seek)is not null
                        union all
                        select xx = max(last_user_scan)
                        where max(last_user_scan)is not null
                        union all
                        select xx = max(last_user_lookup)
                        where max(last_user_lookup) is not null
                        union all
                        select xx =max(last_user_update)
                        where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
group by d.name