Determine SQL Server foreign key name

Question

I'm trying to determine the name of the foreign key on a table but I'm getting wrong values. If I use this example SQL

CREATE TABLE Address 
(
    id INT NOT NULL PRIMARY KEY,
    street VARCHAR NOT NULL
);

CREATE TABLE Person 
(
    id INT NOT NULL PRIMARY KEY,
    orgId INT NOT NULL REFERENCES dbo.Organization (id)
);

SELECT tbl.name TableName, col.name ColumnName, fk.name ForeignKey
FROM sys.tables tbl
JOIN sys.columns col ON tbl.object_id = col.object_id
LEFT OUTER JOIN sys.foreign_keys fk ON tbl.object_id = fk.parent_object_id
WHERE tbl.name = 'Person'

it's telling me the ForeignKey name is the same thing for both the id and the orgId columns of the Person table. Clearly I'm missing an extra join condition, but I can't for the life of me figure out what it is.

Answer 1

your query is missing some part,to see which columns have FK you can use sys.foreign_key_columns view. here is what you need:

SELECT
    tbl.name   TableName
    , col.name ColumnName
    , fk2.name  ForeignKey
FROM
    sys.tables                              tbl
    JOIN sys.columns                        col
        ON tbl.object_id = col.object_id
    LEFT OUTER JOIN sys.foreign_key_columns AS fk
        ON col.object_id = fk.parent_object_id
           AND fk.parent_column_id = col.column_id
    LEFT OUTER JOIN sys.foreign_keys        AS fk2
        ON fk2.object_id = fk.constraint_object_id
WHERE
    tbl.name = 'Person';