How can I tell if a database table is being accessed anymore? Want something like a "SELECT trigger"

Solution 1:

Look in sys.dm_db_index_usage_stats. The columns last_user_xxx will contain the last time the table was accessed from user requests. This table resets its tracking after a server restart, so you must leave it running for a while before relying on its data.

Solution 2:

Re: Profiler, if you monitor for SP:StmtCompleted, that will capture all statements executing within a stored procedure, so that will catch table accesses within a sproc. If not everything goes through stored procedures, you may also need the SQL:StmtCompleted event.

There will be a large number of events so it's probably still not practical to trace over a long time due to the size of trace. However, you could apply a filter - e.g. where TextData contains the name of your table you want to check for. You could give a list of table names to filter on at any one time and work through them gradually. So you should not get any trace events if none of those tables have been accessed.

Even if you feel it's not a suitable/viable approach for you, I thought it was worth expanding on.

Another solution would be to do a global search of your source code to find references to the tables. You can query the stored procedure definitions to check for matches for a given table, or just generate a complete database script and do a Find on that for table names.

Solution 3:

For SQL Server 2008 you should take a look at SQL Auditing. This allows you to audit many things including selects on a table and reports to a file or Events Log.

Solution 4:

The following query uses the query plan cache to see if there's a reference to a table in any of the existing plans in cache. This is not guaranteed to be 100% accurate (since query plans are flushed out if there are memory constraints) but can be used to get some insights on table use.

SELECT schema_name(schema_id) as schemaName, t.name as tableName,
    databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time, 
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time, 
dm_exec_query_stats.total_logical_reads, 
dm_exec_query_stats.total_physical_reads, 
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases ON dm_exec_sql_text.dbid = databases.database_id
RIGHT JOIN sys.tables t (NOLOCK) ON cast(dm_exec_query_plan.query_plan as varchar(max)) like '%' + t.name + '%'