How to find a specific Foreign Key of a table through T-SQL?

Here is another version. You can filter by table and the parent-table/column-name I guess.

SELECT 
  [ForeignKey] = f.name
, [TableName] = OBJECT_NAME(f.parent_object_id), COL_NAME(fc.parent_object_id,fc.parent_column_id)
, [ReferenceTableName] = OBJECT_NAME (f.referenced_object_id)
, ReferenceColumnName = COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
FROM 
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
WHERE name LIKE '%the_fk_name%'

BETTER IDEA:

Name your FK's on creation.

ALTER TABLE [dbo].ChildTable
ADD CONSTRAINT ChildTableToParentTableFK  /* A strong name */
    FOREIGN KEY ( ParentTableKey )
        REFERENCES [dbo].ParentTable ( ParentTableKey  )
GO

Use:

SELECT *
FROM sys.foreign_keys
WHERE name LIKE '%yourForeignKeyName%'

Tags:

Sql

Sql Server