Technical Article

Last Time a Table was Accessed

,

Just run the script on the Database you want to list the tablenames and last time they were accessed, create date, and modify date.

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,
 CASE WHEN so.type = 'U' THEN 'Table (user-defined)'
 WHEN so.type = 'V' THEN 'View'
 END  AS Table_View
 ,CASE WHEN st.create_date IS NULL
 THEN sv.create_date
 ELSE st.create_date
 END AS create_date
 ,CASE WHEN st.modify_date IS NULL
 THEN sv.modify_date
 ELSE st.modify_date
 END AS modify_date

    FROM sys.objects so
    LEFT JOIN LastActivity la
      on so.object_id = la.ObjectID
  LEFT JOIN sys.tables st
  on so.object_id = st.object_id
  LEFT JOIN sys.views sv
  on so.object_id = sv.object_id

   WHERE so.type in ('V','U')
     AND so.object_id > 100

GROUP BY OBJECT_NAME(so.object_id)
, so.type 
,st.create_date 
,st.modify_date
,sv.create_date
,sv.modify_date

ORDER BY OBJECT_NAME(so.object_id)

Rate

2.33 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

2.33 (3)

You rated this post out of 5. Change rating