通过

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 :-)

Comments

  • Anonymous
    June 09, 2008
    is this sql 2008 specific or will it work in 2005? Anyhoo, my major question is why doesn't the server do any built in tracking for tables like indexes?  it's a common enough request you'd think it'd be worth adding support for that?

  • Anonymous
    June 09, 2008
    Its SQL 2008 specific as the audit objects don't exist in 2005. In that case you would have to use trace. Adding it formally to the product is something that has been discussed but did not make it into 2008, we'll see if it makes a future version. Enter it on http://connect.microsoft.com and get others to vote to increase the chance

  • Anonymous
    September 22, 2016
    I've just implemented this one 2 tables we think might be redundant. Thanks - works great!!