Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"?

share|improve this question
    
I started a Profiler session filtering on my login, then went to SSMS and selected View Dependencies (for a table, a view, etc) and captured the code that Microsoft executes behind the scenes. Now whenever I want to know an object's dependencies, I run that script, depending upon the target object. If you do this you'll notice some differences depending upon the object selected. Started to post the script but it was simply to large to fit. –  jl01 Feb 14 '12 at 18:48
    
Jenny is correct. sql_expression_dependencies where referenced_database is not nulll.Yes these will be only tables. loop through all the children and get your cross linked dependencis. If you dont have access to linked servers, then it may not give you the expected results. openquery is captured, so what else are we looking for ?? –  user28213 Sep 13 '13 at 11:04
add comment

11 Answers 11

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.

share|improve this answer
2  
Good point about triggers and just reading from a table. –  Miles D Jan 28 '11 at 19:32
add comment
  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...

share|improve this answer
    
I like the degrade performance answer, +1 –  Larry Smithmier Jan 31 '11 at 4:29
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
    
One of many reasons that SELECT * is evil. Anyone who uses it almost deserves to have his code break when columns are inevitably dropped, renamed, or reordered. It'll be a valuable lesson. –  Jon of All Trades Jan 31 '12 at 17:58
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
That is effectively our current policy; although it is difficult to keep track of those dependencies within the systems (we've something like 6000 stored procedures acting over about 12 databases over 2 instances) –  Rowland Shaw Jan 26 '12 at 13:27
add comment

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

share|improve this answer
    
I thought those tables only handled "on database" dependencies, but wouldn't handle cross-instance dependencies? –  Rowland Shaw Aug 30 '13 at 7:30
add comment

Great tool with are using is Sql Negotiator Pro from www.aphilen.com also Red gate has dependency tracker with is also neat

share|improve this answer
    
Jenny did already sugegst that, but I thought those tables only handled "on database" dependencies, but wouldn't handle cross-instance dependencies? –  Rowland Shaw Sep 12 '13 at 7:43
    
... both were already suggested in well formatted answers... –  dezso Sep 12 '13 at 13:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.