Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
skiabox
Posting Yak Master
169 Posts |
 Posted - 2008-01-08 : 08:47:53
|
| Is there any tool that can find possible relationships between tables?Thank you. |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2008-01-29 : 12:24:34
|
| Based on what?Mark |
 |
|
|
SusanthaB
Starting Member
14 Posts |
Posted - 2008-02-06 : 07:13:17
|
| You can find the relationships of the table by using primary key, foreign key relationships. However, to do that you should have proper naming convention in your database as well as primary key defined in each table.Eg: Employee (Employee_pk, LastName, FirstName, Department_pk) Department (Department_pk, Title)If you have not defined referential constraints, you can still find the relationship with primary key and check the same name appeared in other tables. You can use information_schema.KEY_COLUMN_USAGE catalog view to find out the primary keys of a table. |
|
|
|
danp
Starting Member
3 Posts |
Posted - 2008-02-06 : 09:45:24
|
| Yes... any database modeling tool (Visio, Modelright, ERwin, etc) would be able to reverse engineer your database and show a graphical view of your relationships (i.e. FK constraints). |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-02-06 : 10:17:47
|
| I once worked a little with an HR / Payroll db that was provided to the company from an outside vendor. It had around 1000 tables, 1000 SPs, ect. There were no FKs. All referential integrity was handled via triggers. Almost every table had a trigger that checked parent tables and such to make sure a record existed and so on. It was a bit crazy. I'm not a data modeler but I wouldn't think that there would be any modeling tools out there to help with that. Basically, to create a report or something for them, you had to look at all the triggers of all the tables involved to try to figure out how it's put together. |
|
|
|
danp
Starting Member
3 Posts |
Posted - 2008-02-06 : 10:38:45
|
| Yeah, that's a crazy situations. Using triggers to enforce ref integrity that can be enforced by simple FK constraints is total overkill, error-prone, slow, etc. A bad choice. I think that the original poster was not talking about this kind of situation.BTW, there are tools that can infer FK constraints (which do not exist, but should), but it is a bit error prone and they need to be verified by hand anyway.Hope this helps. |
|
|
|
|
|
|
|
|
Relationship finder