Last Table Select

When was each table last actually read? This query unions the last seek, scan and lookup timestamps from sys.dm_db_index_usage_stats for every user table in the current database and reports the most recent of the three per table. A quick way to spot tables that have not been touched in ages - candidates for archiving.

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)