Skip to main content

SQL Server statement to find foreign key relationships for table in SQL Server.

declare @tableName as varchar(250) = '<TABLE_NAME>';

with cte
    as (
    select cast(object_name(fkc.parent_object_id) as varchar(max)) as TableRelation,
        object_name(fkc.parent_object_id) as DependentTable,
        fkc.parent_object_id as childID,
        1 as ReLevel
    from sys.foreign_key_columns as fkc
    where fkc.referenced_object_id = object_id(@tableName)
    union all
    select cast(c.TableRelation + '-->' + object_name(fkc.parent_object_id) as varchar(max)) as objName,
        object_name(fkc.parent_object_id) as DependentTable,
        fkc.parent_object_id as childID,
        c.ReLevel + 1
    from sys.foreign_key_columns as fkc
         inner join cte as c
            on fkc.referenced_object_id = c.Childid
    and fkc.parent_object_id <> c.childid)
    select distinct DependentTable
    from cte;