Find the Foreign Keys Associated with a Given Primary Key

I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via

SELECT *  
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu 
WHERE EXISTS (
    SELECT tc.* 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
    WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'  
        AND tc.TABLE_NAME = 'MyTable'  
        /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO

but for a PK returned from such a query how do I establish the associated FK (assuming there is one)?

I know you can also get the referenced tables via:

SELECT CONSTRAINT_NAME = name, 
       FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id), 
       FOREIGN_TABLE = OBJECT_NAME(parent_object_id), 
       REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id), 
       REFERENCED_TABLE = OBJECT_NAME(referenced_object_id) 
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO

but I am struggling now to get the explicit column references.

I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. I need all of the constraint information for any given column (if any).

I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs.

Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both.

Here's a simple query to match up foreign keys to their referenced tables/columns:

SELECT
    o1.name AS FK_table,
    c1.name AS FK_column,
    fk.name AS FK_name,
    o2.name AS PK_table,
    c2.name AS PK_column,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.foreign_key_columns fkc
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c1
        ON fkc.parent_object_id = c1.object_id
        AND fkc.parent_column_id = c1.column_id
    INNER JOIN sys.columns c2
        ON fkc.referenced_object_id = c2.object_id
        AND fkc.referenced_column_id = c2.column_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id

The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).

(Edited to add some more output columns.)

EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.

Multi-column keys are shown as comma-separated lists in FK_columns and PK_columns, using the traditional FOR XML/STUFF abuse. The FK_indexes column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it's NULL, then you've got an unindexed foreign key. You can tweak the ORDER BY, or add a WHERE clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.

SELECT
    fk.is_disabled,
    fk.is_not_trusted,
    OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
    o1.name AS FK_table,
    --Generate list of columns in referring side of foreign key
    STUFF(
        (
            SELECT ', ' + c1.name AS [text()]
            FROM sys.columns c1 INNER
                JOIN sys.foreign_key_columns fkc
                    ON c1.object_id = fkc.parent_object_id
                    AND c1.column_id = fkc.parent_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS FK_columns,
    --Look for any indexes that will fully satisfy the foreign key columns
    STUFF(
        (
            SELECT ', ' + i.name AS [text()]
            FROM sys.indexes i
            WHERE i.object_id = o1.object_id
                AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
                    SELECT fkc.constraint_column_id, fkc.parent_column_id
                    FROM sys.foreign_key_columns fkc
                    WHERE fkc.constraint_object_id = fk.object_id
                    EXCEPT
                    SELECT ic.key_ordinal, ic.column_id
                    FROM sys.index_columns ic
                    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                )
            FOR XML PATH('')
        ), 1, 2, '') AS FK_indexes,
    fk.name AS FK_name,
    OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
    o2.name AS PK_table,
    --Generate list of columns in referenced (i.e. PK) side of foreign key
    STUFF(
        (
            SELECT ', ' + c2.name AS [text()]
            FROM sys.columns c2
                INNER JOIN sys.foreign_key_columns fkc
                    ON c2.object_id = fkc.referenced_object_id
                    AND c2.column_id = fkc.referenced_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS PK_columns,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name

StackExchange Database Administrators Q#31720, answer score: 37