I'm dealing with a DB with hundreds of tables, with interconnecting FK references producing potentially thousands of different permutations for how for example table A and table K can join together. Now, I need to create a query that will be given a single table name and it's PK ID, and then using FK references in the DB would find ALL connections regardless of how far down the line it has to go.
Now, I'm not looking for a script, as I understand this will become quite complex. I'm looking for strategic advice on what would be the best way to create such a script, or tips on where to find previous similar examples.
Currently what I have is a procedure that can find all TABLE + PK_ID values referencing, or referenced by one TABLE + PK_ID. And what I was thinking of doing is building up some kind of a looping TSQL that would use that procedure and a number of #temp tables to find the connections in levels. But it's going to be tricky to make this thing truly recursive.
Basically, say I was looking for the connections to and from table A, it could produce a list logically similar to the one below:
A - J I - E
/ \ / \ /
H - B C - D - F - O
\ / /
G ------/
WHILE
loop adding tables to it that are connected to the ones already in it; stop when an iteration doesn't add any tables. Of course, if you want, you can probably do the above in a recursive CTE.