Sql-server – SQL Server – Recursive “find all FK connections for ID through the entire DB” query

recursivereferential-integritysql server

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 ------/

Best Answer

How about implementing a child/parent relationship in a table including the hierarchical structure. You could fill that iteratively for every table in your DB, if you add a parent you'd update all its children's hierarchical structure.

To find specific results for a certain table you'd then simply have to select all records that contain that table in its hierarchy.

(Hierachies in your example would look something like this:

/H/B /H/G/B /H/B/A /H/G/B/A ... )

Related Question