SQL script to get ALL SQL dependencies on a SQL object

Question

Hello,

I have a HUMONGOUS legacy databases with thousands of objects. I really want to delete 113 tables, but I need to get a comprehensive list of the dependencies that would need to either modified or removed as well. Is there any way to do this? The "view dependencies" UI in SSMS is going to take way too long. What I am thinking of is putting the names of the 113 tables in a table, and then joining that with sql_expression_dependencies to get all of the dependencies, and then recursing until I have found them all. This seems to work great, but it doesn't seem to be returning the names of TABLES which depend on my tables. It only returns SPs, FUNCTIONs, etc. Any idea how I can augment this to include all dependency types?

Answer 1

Identifying all the "related" objects within a single SQL Server database -- tables, views, procedures, functions, et. al. (triggers, synonyms, and even more obscure obejcts) is a very difficult thing to do (and all but impossible if your using dynamic SQL). The best solutions I've seen have been third-party utilities that wade through the database and, I have to imagine, brute-force the problem by parsing and checking everything.

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