How can I track database dependencies?

Question

As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods for identifying database dependencies, both within the SQL environment, and maybe onward into things like SSIS?

I've worked places where fairly brutal options have been taken such as:

• Drop first, ask questions later (can kill a data warehouse build if it tries to extract a table that no longer exists)
• Remove permissions first, and wait for the errors to be reported (can cause silent bugs, if the failiure isn't handled correctly)

I appreciate that SQL Server comes with tools for tracking dependencies within that instance, but these seem to struggle if you have databases on different instances. Are there options that make it easier to query dependencies, maybe answering questions like "Where is this column used?" with answers like "Over on this other server in this stored procedure" or "Over in this SSIS package"?

Answer 1

There is no easy way to do this. Triggers don't work, as if you select from a table no trigger is fired. The best way that I've found to do this is to have the developers track what they use. When something is going to be dropped check with all the dev teams, and after everyone signs off, rename the object. Then is nothing breaks for a month or to, the object can be safely dropped.

Answer 2

1. Search code for usage with sys.sql_modules.definition: is it referenced? Then...
2. Check permissions: what client code can call it? Then...
3. Profiler

Thus:

• For a table with no reference and no permissions, it isn't being used.
• With no references and some permissions, run profiler to see usage
• With no permissions and references, add logging of usage

What I've done before is to make the table a view masking the table then making the view perform badly: (cross join itself, distinct). You don't actually remove it but you do generate client timeouts or complaints...

Answer 3

One quick way that I have used in the past (and it really depends on the size of tables, number of indexes performance etc), is to add a trigger, that logs a timestamp when an action is performed on the table. As I've said this can have performance issues, so needs to be treated with caution - also watch your logging table doesn't use identity fields, as this can screw up some old code that uses @@IDENTITY. Of course it may just show that a feature in an application hasn't been used for sometime.

It's very hard to track dependencies when all the code that may hit the database isn't in the database i.e. random clients querying the database.

EDIT: To address the point that a table can't have SELECT triggers, here is another option that should work assuming your tables have indexes (tested in 2008 only).

SELECT          
    last_user_seek,
    last_user_scan,
    last_user_lookup,
    last_user_update
FROM
    sys.dm_db_index_usage_stats AS usage_stats
INNER JOIN
sys.tables AS tables ON tables.object_id = usage_stats.object_id
WHERE
    database_id = DB_ID() AND
    tables.name = 'mytable' 

but note that the usage stats table is cleared when the server is restarted, detached etc. So you would need to set up a job to collect the data. Bit of a hack I know.

Answer 4

One way that I've used in the past was to establish a candidates list of tables to remove and then rename them and look for failures.

How I established the list was:

1. see which tables are not in use in current stored procedures, triggers and functions

2. empty tables (zero records);

3. non referenced tables (tables that don’t have any relationships);

4. see which tables weren’t in use since the DB Server was started(DMVs)

After building the list in a text file I made a batch script that would parse our .cs files (we're having only .net projects) from the local mapped version control folder and see if those tables are used in the .cs files (shouldn't happen, but hey.. I've had surprises). If no, then it's clear, if yes, then we build a list and give to developers to check if that that module is still in use.

So, in short, the previous guys are right, there's no silver bullet.

Answer 5

The policy I'm implementing at my company is to put everything that touches SQL Server under source control, in a central location.

• asp.net projects
• SSRS projects
• SSIS projects
• I even script out all the database objects into a repository of sorts.

I don't have it set up yet, but eventually I want to implement some sort of index/central search mechanism that I could use to search for specific tables, sprocs, etc. We are actually a new SQL Server Shop - converting from FoxPro. So old SQL objects are not much of a problem yet, but I'm planning for the future.

The problem I see with the renaming / tracing approach are that some things only run yearly, and not even every year. Not to mention the various ad-hoc things that people ask you to write, and then ask for again months or years later.

Answer 6

There are a variety of tools and techniques to use in tracking dependencies, involving:

Tools I know of:

• SQL Server Dependency viewer (but can have issues if sp using table was created before table was created)
• Redgate SQL Dependency Tracker (via @Eric Humphrey's answer)
• Resharper (.net tool that can be used to look at calling paths, I think it can be used to track where key SQL calls are used)

Methods

• Code searches for use of SQL objects (replicates some of the tools above though)

• Look at usage statistics (ie: when was a SQL object last called), I use the below SQL:

  SELECT 
      last_execution_time,   
      (SELECT TOP 1 
          SUBSTRING(s2.text,statement_start_offset / 2+1 , 
              ((CASE WHEN statement_end_offset = -1 THEN 
                  (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
              ELSE statement_end_offset END) - statement_start_offset) / 2+1)
      )  AS sql_statement,
      execution_count
  FROM sys.dm_exec_query_stats AS s1 
  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
  WHERE 
      s2.text like '%[OBJECT NAME]%' 
      and last_execution_time > [DATE YOU CARE ABOUT]
  ORDER BY last_execution_time desc
  

Note: The usage stats table is cleared when the server is restarted, detached etc. So you would need to set up a job to collect the data. Bit of a hack I know. (from @Miles D)

Techniques

• Search for last usage (see above usage statistics)
• Search for where it's used (see tools)
• Review code usage with developers (via @MrDenny)
• Rename object (ie: post/prefix with _toBeDropped) and watch for errors
• Change permissions, and watch for errors
• Drop object and pray

Answer 7

Several years ago, I tried to build a tool to check similar stuff. The TL;DR answer is that I found it not possible to do with the available resources at that time.

Where is this column used?

This question gets more complicated when you realize that a number of queries, views and stored procedures use select * from the table that column is resident in. Then you need to look at the programs that use those results - so you need some scanner/indexer/parser capable of reading source code that may be C#, Delphi, Java, VB, ASP (classic) and so on just to try to hunt down every reference to that column. Then you need to analyze those programs to try and identify if that code is even being called any more.

Answer 8

Will not handle out of SQL references, but you might want to check out Redgate's SQL Dependency Tracker. It's a nice visualization tool.

Answer 9

This isn't really an answer to your question, but I think it bears mentioning: this is one reason why all systems outside your database should communicate via views and sprocs. You have the build scripts for these in searchable .sql files, so you can easily see if a particular table or column is being used externally.

Of course SSIS will normally connect directly to tables, so this probably isn't much help to your need right now. But when developers connect to your database and complain about having to wait for you (or whomever is serving as DBA) to make the views and sprocs they need, you can tell them: "Any table or column may be deleted or renamed. I'm only obligated to keep you informed of changes to views and sprocs." And they only have to do regression testing for these specific changes.

Answer 10

TSQL the following can be used sys.dm_sql_referencing_entities or sys.sql_expression_dependencies

Alternatively tools such as SQL Negotiator Pro, Redgate etc can generate this visually for you using a GUI