Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ------/
share|improve this question
    
"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 at 12:40

2 Answers 2

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

share|improve this answer
    
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 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 at 14:57

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'
share|improve this answer
    
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 at 8:22
    
Right, seems like the ones above can only resolve references from other types of objects, such as procedures. –  Kahn Mar 3 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 at 16:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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