SQL Server : How do I determine if table1.col1 has a foreign key constraint on table2.col2

Question

Given table1.column1 I need to determine if it has a foreign key into table2.column2.

This SQL will be run many times so it will need to be pretty quick.

I've seen some solutions to this but the tend to be more complicated than I need (i.e. all foreign keys in the db or all foreign keys on a table)

thanks!

You can look at sys.foreign_keys (https://msdn.microsoft.com/en-us/library/ms189807.aspx) and sys.foreign_key_columns (https://msdn.microsoft.com/en-us/library/ms186306.aspx) — Sean Lange, Mar 10 '16 at 18:07

score 2 · Accepted Answer · answered Mar 10 '16 at 18:16

Here is the query I prefer to use, made into a view:

CREATE VIEW vwForeignKeys AS
SELECT
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName,
   OBJECT_NAME (f.referenced_object_id) ReferencedTableName,
   COL_NAME(fc.referenced_object_id,fc.referenced_column_id) ReferencedColName,
   f.Name
FROM
   sys.foreign_keys AS f
INNER JOIN
   sys.foreign_key_columns AS fc
     ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
   sys.tables t
      ON t.OBJECT_ID = fc.referenced_object_id

In your case, a query would then look like

IF EXISTS(
   SELECT 1 
   FROM 
      vwForeignKeys 
   WHERE 
      TableName='table1' 
      AND ColName='column1' 
      AND ReferencedTableName='table2' 
      AND ReferencedColName='column2'
) THEN 
BEGIN
   --FOREIGN KEY EXISTS, DO SOMETHING
END

SQL Server : How do I determine if table1.col1 has a foreign key constraint on table2.col2

1 Answers1