List All Foreign Keys Referencing A Table In SQL Server

Frequently I have come across situations to list down all the tables referencing a specific table using foreign key. There are several methods to find the foreign keys referencing a table. Here I will go over the different methods to find the foreign keys references to a specific table.

Using sp_fkey

One among the easiest way to list all foreign key referencing a table is to use the system stored procedure sp_fkey. Here is an example of using sp_fkey.

use WideWorldImporters
EXEC sp_fkeys @pktable_name = 'Orders',
	@pktable_owner = 'Sales'
GO

Using sp_help

Another quick method to find the list of tables referring a table with foreign key is to use the system procedure sp_help with the table name. Here is an example of using sp_help. Once you execute the sp_help with the table name, it will list down all the details of the table including the list of foreign keys referencing the table under the section Table is referenced by foreign key. Here you can see the list of tables and the names of the foreign keys referencing our table.

use WideWorldImporters
EXEC sp_help 'Sales.Orders'
GO

Using SSMS GUI

Another option is use the View Dependencies GUI in SSMS. Here steps to list the tables and other objects referencing our table.

1. From the SSMS Object Explorer, right-click the table and from the context menu select View Dependencies.
2. In the Object Dependencies pop-up screen, make sure the option Objects that depends on […] is selected. Under the Dependencies section, you can see the list of tables and other objects referencing the selected table.

Using sys.foreign_key_columns

Finally a T-SQL method to get the list of referencing tables and foreign key names using the system view sys.foreign_key_columns.

USE WideWorldImporters
SELECT 
	OBJECT_NAME(referenced_object_id) as 'Referenced Object',
	OBJECT_NAME(parent_object_id) as 'Referencing Object',
	COL_NAME(parent_object_id, parent_column_id) as 'Referencing Column Name',
	OBJECT_NAME(constraint_object_id) 'Constraint Name'
FROM sys.foreign_key_columns
WHERE OBJECT_NAME(referenced_object_id) = 'Orders'
GO

From the above methods, choose your proffered method to get a list of all foreign keys referencing a table.

Reference

• Details about sp_fkeys at Microsoft Docs.