Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· openaiHugging Face Suzhen/CodeChat

in sql server, is there a way to get the entire chain of foreign keyed tables starting…

  1. User

    in sql server, is there a way to get the entire chain of foreign keyed tables starting from a specific table?

  2. Assistant

    In SQL Server, there isn't a built-in query to get the entire chain of foreign keyed tables starting from a specific table. However, you can use Common Table Expressions (CTE) and system catalog views to achieve this.

    Here's a script that does this:

    -- Replace 'YourTableName' with the starting table name you want to find the chain for
    DECLARE @TableName NVARCHAR(128) = 'YourTableName';
    
    WITH CTE_FK_Chain (ParentTable, ParentColumn, ReferencedTable, ReferencedColumn, Level)
    AS (
        -- Base case: Get tables directly referenced by the starting table
        SELECT 
            OBJECT_NAME(fk.parent_object_id) AS ParentTable,
            COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
            OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
            COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn,
            1 AS Level
        FROM sys.foreign_keys fk
        INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        WHERE OBJECT_NAME(fk.parent_object_id) = @TableName
    
        UNION ALL
    
        -- Recursive case: Keep finding tables referenced by the current Table
        SELECT 
            OBJECT_NAME(fk.parent_object_id),
            COL_NAME(fkc.parent_object_id, fkc.parent_column_id),
            OBJECT_NAME(fk.referenced_object_id),
            COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id),
            c.Level + 1
        FROM sys.foreign_keys fk
        INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        INNER JOIN CTE_FK_Chain c ON c.ReferencedTable = OBJECT_NAME(fk.parent_object_id)
    )
    
    SELECT * 
    FROM CTE_FK_Chain
    ORDER BY Level;
    

    This will give you the chain of foreign keyed tables in a hierarchical format, starting from the specified table. The Level column indicates the depth of the foreign key relationship.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.