How to dynamically navigate hierarchy of table foreign key relationships in SQL Server database to get ultimate parent entry?

Question

I work at a company that has a project management department which needs to receive periodic email updates about changes that occur in our database. The database is structured with a fair amount of foreign key relationships that result in a somewhat complex set of relationships that all sit underneath a 'Project' table.

Furthermore, we have a 'Contact' table which is related in a many-to-many fashion to our 'Project' table via a 'Contact_Relationship' table. This table maintains the type of the relationship as well as the relationship itself. Assume every other subtable, subsubtable, etc. is in a one-to-many relationship with the table above it in the hierarchy.

See here for an overly simplified example:

Project
     Contact_Relationship
     Subtable1
          Subsubtable1
     Subtable2
          Subsubtable2
          Subsubtable3

I have been tasked with implementing an update messaging system that polls periodically for changes to either a project entry or any of its subitems and posts a message. To accomplish this, I need to implement a procedure to get the ultimate parent entry in the 'Project' table of an entry in any of the applicable tables, find the 'Contact_Relationship' entries where the type is "Project manager", and then return those entries so that I can post an update message with the information to the applicable contacts. Now, given that the structure of the subtables, subsubtables, etc. in the database is in continuous development and will certainly see more tables added in the future, is there a way to build a dynamic query using some sort of cross apply on SQL server's administrative tables to get at the ultimate parent entry from any potential subtable or subsubtable? Am I way off-base here and there's a much simpler and more obvious solution? I've been stuck on this problem for a while now and am beginning to run up on deadlines to get this whole thing implemented, and I'm worried the SQL knowledge required here is currently beyond my reach.

Unlike the question Get Root parent of child in Hierarchical table, in this case I am traversing foreign key relationships between tables, rather than a parent-child relationship in the same table.

Does this answer your question? [Get Root parent of child in Hierarchical table](https://stackoverflow.com/questions/24523560/get-root-parent-of-child-in-hierarchical-table) — PM 77-1, Jan 06 '20 at 19:25
No, because this answer assumes that all of the items are located in the same table, and that the path is equivalent at all levels. In my situation, I am traversing foreign key relationships between tables, rather than a parent-child relationship in the same table. — t-schreibs, Jan 06 '20 at 19:52

score 2 · Accepted Answer · answered Jan 06 '20 at 20:13

You can apply the heirarchical navigation idea from the same table, to the tables in INFORMATION_SCHEMA. For instance, this (taken from 864696):

WITH MyCTE AS (
SELECT 
     KCU1.CONSTRAINT_SCHEMA AS FK_CONSTRAINT_SCHEMA 
    ,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
    ,KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
    ,KCU2.CONSTRAINT_SCHEMA AS REFERENCED_CONSTRAINT_SCHEMA 
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
    ,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA 
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
)
...

Now you can apply the hierarchical navigation to this.

Brilliant, I've used this to create a view, and am now able to dynamically create a path to the 'Project' table by selecting from it in a CTE expression like the one in [Get Root parent of child in Hierarchical table](https://stackoverflow.com/questions/24523560/get-root-parent-of-child-in-hierarchical-table). — t-schreibs, Jan 06 '20 at 21:26
And because it uses `INFORMATION_SCHEMA` this also works on Postgresql. — RonJohn, Jan 09 '23 at 18:38

How to dynamically navigate hierarchy of table foreign key relationships in SQL Server database to get ultimate parent entry?

1 Answers1