Finding SQL Server Tables that are not used since last SQL Server restart


  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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: