SQL 2008: How do I tell if a specific table is still being used in an app?

Ever had to take over the code for a system you did not define?

Ever found the documentation for such an app, to be ahemmm lacking?

Its  a fairly common ask from new app developers/ DBAs to find out if certain code is still being used for apps that have evolved over  a number of years. In SQL 2005 we added a DMV that allows you to work out if an index is being used or not. However the question still comes up about tables.

This came up recently on our internal SQL 2008 alias in the context of using sp_trace_setfilter, but Jack came up with  a better solution using audit.

create server audit unused_tables to file (‘c:\temp’)

go

alter server audit unused_tables with (state = on)

go

use database pubs

go

create database audit specification unused_tables for server audit unused_tables

add(select on authors by public),

add(insert on authors by public),

add(update on authors by public),

add(delete on authors by public)

go

alter database audit specification unused_tables with (state = on)

go

Then to check on it:

select * from sys.fn_get_audit_file(‘c:\temp\*.aud’, NULL, NULL)

go

Of course having found out that a bunch of tables are not being used what do you do? If you delete them the above data helps you feel pretty confident that they are not being used but its not perfect (classic example is the end of month, end of year reports...).

Hence I'll leave this as an exercise for the reader :-)