How to find a specific table foreign key through T-SQL?

Question

I want to add one foreign key from several tables. in the GUI in the sql server, if we expand the table parameters, we will 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. The customer has the identifier Customer_ID as primary, the items have Item_ID as primary, and Orders has the Order_ID as primary, the foreign key Customer_nbr refers to the customer (Customer_ID) and foreign key item_nbr links Items (Item_ID).

therefore, in this folder "Keys" there are names for such keys as: "FK_Orders_Customer__38996AB5"

the problem is that the number "38996AB5" that follows the customer’s word is not fixed, if you delete the restrictions and then restore them, they will be changed (for security reasons or so).

so I'm looking for an SQL command that can be entered to get this key no matter how it was. logically, I could use the keyword "LIKE" (that is ... like = 'FK_Orders_Customer __%'), but I could not know how and where to put it.

early.

Answer 1

Using:

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

Answer 2

Here is another version. You can filter by table and by parent table / column name.

 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%' 

BEST IDEA:

Name your FK at creation.

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