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

Question

i want to bring ONE foreign key from several ones in a table. in the GUI interface in sql server if we expand the table options we can see a folder called "Keys", in this folder there is one primary key and several foreign ones. In my example i have three tables, Customer, Orders and Items. Customer has Customer_ID as primary, Items has Item_ID as primary and Orders has Order_ID as primary, foreign key Customer_nbr references customer (Customer_ID) and foreign key item_nbr references Items (Item_ID).

so in that "Keys" folder there are names for those keys like: "FK_Orders_Customer__38996AB5"

the problem is that the number "38996AB5" that follows the customer word is not fixed, if you delete constraints then re-establish them it will be changed (for security reasons or so).

so i am looking for an SQL command that can be typed in order to get that key no matter what it was like. logically i could use the keyword "LIKE" (i.e. ... like = 'FK_Orders_Customer__%') but i could not know how or where to put it.

thanks in advance.

Answer 1

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

Answer 2

Use:

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