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