How can I track database dependencies?

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

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.

StackExchange Database Administrators Q#972, answer score: 14