2

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 ------/
Improve this question
1
  • "But it's going to be tricky to make this thing truly recursive." - don't. Make it iterative. Keep a table variable containing all the tables you've found so far; have a 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.
    – AakashM
    Feb 28, 2014 at 12:40

2 Answers 2

Reset to default
1

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 ... )

Improve this answer
2
  • We can't really make any substantial datamodel changes to accommodate this script, and a hierarchial table describing all potential pathways would basically be neverending. As I said, the number of tables and cross connections is quite staggering. Granted, the script needs to do some of this regardless, but the point is that eventually it will run out of connections for the originally provided ID. Otherwise it would basically have to map the entire DB with all its variations.
    – Kahn
    Feb 28, 2014 at 13:48
  • The iterative approach I described would be finite by definition, as each table generates a finite number of entries (# of FK values * # of already existing entries for each of the FK's parent tables). Thus of course loops (H-G-B vs. H-B) are blowing up your table somewhat, but IMO the issues those would cause for a recursive approach would be worse.
    – Sascha Rambeaud
    Feb 28, 2014 at 14:57
1

How about just using the native feature that does this? You can simply call sp_depends and it'll output the objects which depend on that object. Then run it again for each of those objects. A simple loop will handle the recursion logic and just drop the values into a table so that you can return everything at the end. As long as you've got your foreign keys defined properly this stored procedure will work just fine.

exec sp_depends 'Person.Person'
Improve this answer
3
  • Since this is not going to be supported in the future, I tried to check into using sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities, but neither of them seem to work in SQL Server 2008 or 2012. I wonder why?
    – Kahn
    Mar 3, 2014 at 8:22
  • Right, seems like the ones above can only resolve references from other types of objects, such as procedures.
    – Kahn
    Mar 3, 2014 at 8:27
  • sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities only work when foreign keys are in place and when the code isn't using dynamic SQL. sp_depends is deprecated but it hasn't been removed yet. As far as I know it's still there in SQL 2014 which means it'll be around for at least 2 more years.
    – mrdenny
    Mar 3, 2014 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.