question

manish19 avatar image
manish19 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
dependencyreferential-integritydata-integrity
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
I understand your point.. agreed and actioned thumbs up.. but in this case none of the answers led me to the solution.. I am working on a customized script. If at all I get success, I would share that here.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Right. That's actually one of the exceptions to the guidelines, and makes the job that much tougher.
1 Like 1 ·
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.
0 Likes 0 ·
Do you have ER diagram of the data model with relationships. It can help you to see the relationships defined in the diagram.
0 Likes 0 ·
@Venkataraman - I am looking of some kind of automation to do the job.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.