If you need to return all foreign keys that reference a given table in SQL Server, try one of the following methods.

The first method queries the sys.foreign_keys system view. The second method executes the sp_fkeys system stored procedure.

Option 1 – sys.foreign_keys

The following code retrieves all foreign keys that reference a given table, along with the primary key and foreign key tables. I also include the schema for the foreign key tables.

USE WideWorldImportersDW;
SELECT 
  OBJECT_NAME(referenced_object_id) AS [PK Table],
  name AS [Foreign Key],
  SCHEMA_NAME(schema_id) AS [FK Schema],
  OBJECT_NAME(parent_object_id) AS [FK Table]
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('Dimension.City');

Result:

+------------+---------------------------------------+-------------+------------+
| PK Table   | Foreign Key                           | FK Schema   | FK Table   |
|------------+---------------------------------------+-------------+------------|
| City       | FK_Fact_Order_City_Key_Dimension_City | Fact        | Order      |
| City       | FK_Fact_Sale_City_Key_Dimension_City  | Fact        | Sale       |
+------------+---------------------------------------+-------------+------------+

In this case I’m using the WideWorldImportersDW database, and I’m returning the foreign keys that reference the Dimension.City table (the Dimension.City table contains the primary key column that the foreign keys reference).

Option 2 – sp_fkeys

Another way to get the foreign keys that reference a particular table is to use the sp_fkeys system stored procedure. This stored procedure provides us with a choice of getting foreign keys based on (among other things) the referenced table, or the referencing table.

In this case we’re interested in getting the foreign keys that reference a given table, so we can do this:

EXEC sp_fkeys 
  @pktable_name = 'City', 
  @pktable_owner = 'Dimension';

Result (using vertical output):

-[ RECORD 1 ]-------------------------
PKTABLE_QUALIFIER | WideWorldImportersDW
PKTABLE_OWNER     | Dimension
PKTABLE_NAME      | City
PKCOLUMN_NAME     | City Key
FKTABLE_QUALIFIER | WideWorldImportersDW
FKTABLE_OWNER     | Fact
FKTABLE_NAME      | Order
FKCOLUMN_NAME     | City Key
KEY_SEQ           | 1
UPDATE_RULE       | 1
DELETE_RULE       | 1
FK_NAME           | FK_Fact_Order_City_Key_Dimension_City
PK_NAME           | PK_Dimension_City
DEFERRABILITY     | 7
-[ RECORD 2 ]-------------------------
PKTABLE_QUALIFIER | WideWorldImportersDW
PKTABLE_OWNER     | Dimension
PKTABLE_NAME      | City
PKCOLUMN_NAME     | City Key
FKTABLE_QUALIFIER | WideWorldImportersDW
FKTABLE_OWNER     | Fact
FKTABLE_NAME      | Sale
FKCOLUMN_NAME     | City Key
KEY_SEQ           | 1
UPDATE_RULE       | 1
DELETE_RULE       | 1
FK_NAME           | FK_Fact_Sale_City_Key_Dimension_City
PK_NAME           | PK_Dimension_City
DEFERRABILITY     | 7

This can easily be switched around to search for foreign keys based on the foreign key table by simply replacing the parameters with @fktable_name and @fktable_owner:

EXEC sp_fkeys 
  @fktable_name = 'Order', 
  @fktable_owner = 'Fact';

A True/False Check

If you simply want to know whether or not a table is referenced by a foreign key, but you don’t want it all listed out, see Check if a Table is Referenced by a Foreign Key in SQL Server with OBJECTPROPERTY().

That article uses the TableHasForeignRef argument of the OBJECTPROPERTY() function to return 1 if the table is referenced by a foreign key, and 0 if it isn’t.