Given a SourceTable and a TargetTable, I would like to programmatically create a string with all joins required.
In short, I am trying to find a way to create a string like this:
FROM SourceTable t
JOIN IntermediateTable t1 on t1.keycolumn = t.keycolumn
JOIN TargetTable t2 on t2.keycolumn = t1.keycolumn
I have a query that returns all foreign keys for a given table, but am running into limitations in trying to run through all this recursively to find the optimal join path and make the string.
SELECT
p.name AS ParentTable
,pc.name AS ParentColumn
,r.name AS ChildTable
,rc.name AS ChildColumn
FROM sys.foreign_key_columns fk
JOIN sys.columns pc ON pc.object_id = fk.parent_object_id AND pc.column_id = fk.parent_column_id
JOIN sys.columns rc ON rc.object_id = fk.referenced_object_id AND rc.column_id = fk.referenced_column_id
JOIN sys.tables p ON p.object_id = fk.parent_object_id
JOIN sys.tables r ON r.object_id = fk.referenced_object_id
WHERE fk.parent_object_id = OBJECT_ID('aTable')
ORDER BY ChildTable, fk.referenced_column_id
I am sure this has been done before, but I can't seem to find an example.
Best Answer
I had a script that does a rudimentary version of foreign key traversal. I adapted it quickly (see below), and you might be able to use it as a starting point.
Given a target table, the script attempts to print the join string for the shortest path (or one of them in the case of ties) for all possible source tables such that single-column foreign keys can be traversed to reach the target table. The script seems to be working well on the database with a couple thousand tables and many FK connections that I tried it on.
As others mention in the comments, you'd need to make this more complex if you need to handle multi-column foreign keys. Also, please be aware that this is not by any means production-ready, fully-tested code. Hope it's a helpful starting point if you do decide to build out this functionality!