Sql-server – How to write a query which recursively returns all foreign key references to a column

foreign keysql serversql-server-2012

I need to obtain a list of foreign keys which refer to a given column, as well as all foreign keys which refer to those keys, and so on. Order does not matter. I have

SELECT
    s.name,
    t.name,
    c.name,
    fk.name
FROM
    sys.foreign_key_columns fkc
    INNER JOIN sys.foreign_keys fk
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c
        ON c.object_id = fkc.parent_object_id
        AND c.column_id = fkc.parent_column_id
    INNER JOIN sys.tables t
        ON t.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas s
        ON s.schema_id = t.schema_id
WHERE
    fkc.referenced_object_id = OBJECT_ID(N'dbo.MyTable')
    AND fkc.referenced_column_id = (
        SELECT TOP 1
            column_id
        FROM
            sys.columns
        WHERE
            name = 'MyColumn'
            AND object_id = OBJECT_ID(N'dbo.MyTable')
    )

However, this just returns the foreign keys which immediately reference the column, not the entire tree of foreign keys referencing this column.

Best Answer

Use a recursive CTE, it's essentially no different to the examples that involve HumanResources.Employee, except that you're joining across more tables. Instead of EmployeeIDs, you're collecting object_id and column_id combinations.

Related Question