Technical Article

Find tables unused since last SQL Server restart

,

This script will return a list of user tables in the database, along with the last time a SELECT was executed against them (including any views that include data from that table). This can be used to determine if a table is not in use anymore.

Statistics are reset when the SQL Server sevice is restarted, so this query will only return activity since that time. Also, just because there's no activity doesn't mean it's safe to remove an object - some tables may only be used during monthly or annual processes, and so they wouldn't show any activity except during those brief intervals.

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)

Rate

4.08 (25)

You rated this post out of 5. Change rating

Share

Share

Rate

4.08 (25)

You rated this post out of 5. Change rating