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.