title: How can I tell if a database table is being accessed anymore? Want something like a "SELECT trigger"
body: <p>I have a very large database with hundreds of tables, and after many, many product upgrades, I'm sure half of them aren't being used anymore. How can I tell if a table is is actively being selected from? I can't just use Profiler - not only do I want to watch for more than a few days, but there are thousands of stored procedures as well, and profiler won't translate the SP calls into table access calls.</p>
  
  <p>The only thing I can think of is to create a clustered index on the tables of interest, and then monitor the <code>sys.dm_db_index_usage_stats</code> to see if there are any seeks or scans on the clustered index, meaning that data from the table was loaded. However, adding a clustered index on every table is a bad idea (for any number of reasons), as isn't really feasible.</p>
  
  <p>Are there other options I have? I've always wanted a feature like a "SELECT trigger", but there are probably other reasons why SQL Server doesn't have that feature either.</p>
  
  <p><strong>SOLUTION:</strong></p>
  
  <p>Thanks, Remus, for pointing me in the right direction. Using those columns, I've created the following SELECT, which does exactly what I want.</p>
  
  <pre><code>  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 &gt; 100
  GROUP BY OBJECT_NAME(so.object_id)
  ORDER BY OBJECT_NAME(so.object_id)
  </code></pre>