SQL script to get ALL SQL dependencies on an SQL object

Question

I have a HUMONGOUS obsolete database with thousands of objects. I really want to delete 113 tables, but I need to get a complete list of dependencies that will need to be either changed or deleted. Is there any way to do this? The user interface "view dependencies" in SSMS will be too long. What I'm thinking of is putting the names of 113 tables into a table and then joining this with sql_expression_dependencies to get all the dependencies and then recurse until I find them. This seems to work fine, but it looks like it is not returning table names that depend on my tables. It returns only SP, FUNCTION, etc. Any idea how I can increase this to include all types of dependencies?

Answer 1

Identification of all related objects in a single SQL Server database - tables, views, procedures, functions, etc. etc. (triggers, synonyms and even more obscure obejcts) is a very difficult task (and almost impossible if you use dynamic SQL). The best solutions that I have seen are third-party utilities that make their way through the database, and I must imagine that this rudely causes the problem to analyze and verify everything.

I used a very impressive tool in SQL 7.0 / 2000 days ("SQL Cast", as I recall), but it doesn't seem to exist anymore. RedGate now has such a tool, SQL Dependency Tracker , but I have not used it.