Below are two methods you can use to return a list of foreign keys for a given table in SQL Server.
This is similar to returning the foreign keys based on the referenced/primary key table, except here, I’m returning the foreign keys based on the referencing/foreign key table itself.
Option 1 – sys.foreign_keys
The following code retrieves all foreign key constraints on the given table, along with the referenced tables.
USE WideWorldImportersDW;
SELECT
OBJECT_NAME(parent_object_id) AS [FK Table],
name AS [Foreign Key],
OBJECT_NAME(referenced_object_id) AS [PK Table]
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('Fact.Order');
Result:
+------------+---------------------------------------------------+------------+ | FK Table | Foreign Key | PK Table | |------------+---------------------------------------------------+------------| | Order | FK_Fact_Order_City_Key_Dimension_City | City | | Order | FK_Fact_Order_Customer_Key_Dimension_Customer | Customer | | Order | FK_Fact_Order_Stock_Item_Key_Dimension_Stock Item | Stock Item | | Order | FK_Fact_Order_Order_Date_Key_Dimension_Date | Date | | Order | FK_Fact_Order_Picked_Date_Key_Dimension_Date | Date | | Order | FK_Fact_Order_Salesperson_Key_Dimension_Employee | Employee | | Order | FK_Fact_Order_Picker_Key_Dimension_Employee | Employee | +------------+---------------------------------------------------+------------+
In this case I’m using the WideWorldImportersDW database, and I’m returning the foreign keys for the Fact.Order table.
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 based on the foreign key table, so we can do this:
EXEC sp_fkeys @fktable_name = 'Order', @fktable_owner = 'Fact';
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 | Customer PKCOLUMN_NAME | Customer Key FKTABLE_QUALIFIER | WideWorldImportersDW FKTABLE_OWNER | Fact FKTABLE_NAME | Order FKCOLUMN_NAME | Customer Key KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Fact_Order_Customer_Key_Dimension_Customer PK_NAME | PK_Dimension_Customer DEFERRABILITY | 7 -[ RECORD 3 ]------------------------- PKTABLE_QUALIFIER | WideWorldImportersDW PKTABLE_OWNER | Dimension PKTABLE_NAME | Date PKCOLUMN_NAME | Date FKTABLE_QUALIFIER | WideWorldImportersDW FKTABLE_OWNER | Fact FKTABLE_NAME | Order FKCOLUMN_NAME | Order Date Key KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Fact_Order_Order_Date_Key_Dimension_Date PK_NAME | PK_Dimension_Date DEFERRABILITY | 7 -[ RECORD 4 ]------------------------- PKTABLE_QUALIFIER | WideWorldImportersDW PKTABLE_OWNER | Dimension PKTABLE_NAME | Date PKCOLUMN_NAME | Date FKTABLE_QUALIFIER | WideWorldImportersDW FKTABLE_OWNER | Fact FKTABLE_NAME | Order FKCOLUMN_NAME | Picked Date Key KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Fact_Order_Picked_Date_Key_Dimension_Date PK_NAME | PK_Dimension_Date DEFERRABILITY | 7 -[ RECORD 5 ]------------------------- PKTABLE_QUALIFIER | WideWorldImportersDW PKTABLE_OWNER | Dimension PKTABLE_NAME | Employee PKCOLUMN_NAME | Employee Key FKTABLE_QUALIFIER | WideWorldImportersDW FKTABLE_OWNER | Fact FKTABLE_NAME | Order FKCOLUMN_NAME | Salesperson Key KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Fact_Order_Salesperson_Key_Dimension_Employee PK_NAME | PK_Dimension_Employee DEFERRABILITY | 7 -[ RECORD 6 ]------------------------- PKTABLE_QUALIFIER | WideWorldImportersDW PKTABLE_OWNER | Dimension PKTABLE_NAME | Employee PKCOLUMN_NAME | Employee Key FKTABLE_QUALIFIER | WideWorldImportersDW FKTABLE_OWNER | Fact FKTABLE_NAME | Order FKCOLUMN_NAME | Picker Key KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Fact_Order_Picker_Key_Dimension_Employee PK_NAME | PK_Dimension_Employee DEFERRABILITY | 7 -[ RECORD 7 ]------------------------- PKTABLE_QUALIFIER | WideWorldImportersDW PKTABLE_OWNER | Dimension PKTABLE_NAME | Stock Item PKCOLUMN_NAME | Stock Item Key FKTABLE_QUALIFIER | WideWorldImportersDW FKTABLE_OWNER | Fact FKTABLE_NAME | Order FKCOLUMN_NAME | Stock Item Key KEY_SEQ | 1 UPDATE_RULE | 1 DELETE_RULE | 1 FK_NAME | FK_Fact_Order_Stock_Item_Key_Dimension_Stock Item PK_NAME | PK_Dimension_Stock_Item DEFERRABILITY | 7
This can easily be switched around to search for foreign keys based on the primary key table by simply replacing the parameters with @pktable_name and @pktable_owner:
EXEC sp_fkeys @pktable_name = 'City', @pktable_owner = 'Dimension';
A True/False Check
If you simply want to know whether or not a table has a foreign key, but you don’t want it all listed out, see Check Whether a Table Has a Foreign Key in SQL Server with OBJECTPROPERTY().
That article uses the TableHasForeignKey argument of the OBJECTPROPERTY() function to return 1 if the table has a foreign key, and 0 if it doesn’t.