How can I find out what FOREIGN KEY constraint references a table in SQL Server?

Solution 1:

Here it is:

SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
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
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

This way, you'll get the referencing table and column name.

Edited to use sys.tables instead of generic sys.objects as per comment suggestion. Thanks, marc_s

Solution 2:

Another way is to check the results of

sp_help 'TableName'

(or just highlight the quoted TableName and press ALT+F1)

With time passing, I just decided to refine my answer. Below is a screenshot of the results that sp_help provides. A have used the AdventureWorksDW2012 DB for this example. There is numerous good information there, and what we are looking for is at the very end - highlighted in green:

Solution 3:

Try this

SELECT
  object_name(parent_object_id) ParentTableName,
  object_name(referenced_object_id) RefTableName,
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Tablename')