Find foreign key membership including owner in SQL Server Was hoping someone could sanity-check this SQL statement.  I need to find the owner (schema), table, and column on both sides of a foreign key:

SELECT
    s1.name AS FK_schema,
    o1.name AS FK_table,
    c1.name AS FK_column,
    fk.name AS FK_name,
    s2.name AS PK_schema,
    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.schemas s1
        ON o1.schema_id = s1.schema_id
    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.schemas s2
        ON o2.schema_id = s2.schema_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 s1.name, o1.name, s2.name, o2.name, fkc.constraint_column_id
The query will return the schema name properly.  However, note that a foreign key may not necessarily reference the primary key; a unique constraint or unique index may also be referenced.  I suggest renaming the PK_Name column to a more generic name like KEY_Name to more accurately describe the column in cases where it's a unique constraint or index instead of primary key.

With a foreign key referencing a unique index, the relationship will be missed entirely by the query because unique indexes are not reflected in sys.key_constraints.  Consider using sys.indexes instead of sys.key_constraints to avoid that problem.

SELECT
    s1.name AS FK_schema,
    o1.name AS FK_table,
    c1.name AS FK_column,
    fk.name AS FK_name,
    s2.name AS PK_schema,
    o2.name AS PK_table,
    c2.name AS PK_column,
    i.name AS KEY_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.schemas s1
        ON o1.schema_id = s1.schema_id
    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.schemas s2
        ON o2.schema_id = s2.schema_id
    INNER JOIN sys.indexes i
        ON fk.referenced_object_id = i.object_id
        AND fk.key_index_id = i.index_id
ORDER BY s1.name, o1.name, s2.name, o2.name, fkc.constraint_column_id;