asked
How to find dependency among tables when there is no referential integrity defined among tables?
We have a database that has many tables that are populated based on the data in each other using stored procedures, functions and views. But there is no foriegn key/ referential integrity defined to tell us the dependencies. Is there a way to find out dependencies among these tables using TSQL? Regards, Manish
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
I would use a tool like SQL Dependency Tracker from Red Gate to find the procs/views/functions that reference the tables - from there on in it would be manual checking. And when you've finished, document it all!
+1 for Dependency Tracker. I just opened it up to see if it had the capability of matching based on column names, but it doesn't. Prompt can do that, but it's going to be table by table to get the suggestions... blech.
Nope. The best you can do is look to see if there are common column names in use between the tables. Hopefully, whoever built this, while deviating from rock-solid common sense and best practice, used the standard method of naming the foreign key columns the same name as the primary key columns. If not, you really have nothing at all.
Right. That's actually one of the exceptions to the guidelines, and makes the job that much tougher.
Thanks Grant! However, the column names for 2 logically linked tables are not same for valid reasons like date_id in calendar table is referenced into transaction table to track transaction start date (start_date_id), expected end date (exp_end_date_id), end date (end_date_id) etc.
Do you have ER diagram of the data model with relationships. It can help you to see the relationships defined in the diagram.
14 People are following this question.
