How to find the join path from one table to another in a large database

Question

Is there an easy way (or tool) for finding the join path from one table to another in a large database?

I'm currently working on a project whose database has over 150 tables. Below is sort of the use case for what I'm trying to do.

Use Case:

Input

• Select Table A
• Select Table B

Output

• Prints out all available paths between the tables.
• Prints out most efficient route.

Answer 1

Since I assume you have some very complex paths through the database, you won't be able to do it with just one query or even a few queries. I've done it (with a project I inherited), and learned some interesting things. But I had to write a program to do it.

What I did was use the schema views Diego references in his answer, and apply some methods for solving graph theory problems (since that's essentially what you've got, here, with the tables being nodes and the foreign keys links in a graph.)

Basically, if I remember correctly (this was a few years ago), you start with one table and then process all its foreign keys by putting the name of the other table each references into a queue. Check for self-references and loops (you'll need a hash set or list of the tables you have processed.) Then pop the next table off the queue and repeat. Eventually you will either encounter the other table or have processed every table you can "reach" from your original table.