How to find all the Foreign Keys that are referencing a Table?

At some point you might find yourself in a situation where you need to figure out how many foreign keys are referencing a certain table.

In this blog post I discuss two ways to achieve this:
1. Querying the sys.foreign_keys system table
2. Executing the sp_fkeys system stored procedure

Querying the sys.foreign_keys system table

As the name implies, the sys.foreign_keys table contains information regarding foreign key constraints. The table contains a column named ‘referenced_object_id’ that is useful to query to find which foreign key constraints are referencing a certain table.

Using AdventureWorks2019 database as an example, I query the sys.foreign_keys table to find all the foreign keys that reference the table ‘Person.Person’.

SELECT name AS [Foreign Key],
SCHEMA_NAME(schema_id) AS [Schema],
OBJECT_NAME(parent_object_id) AS [Table]
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('Person.Person');

I use the OBJECT_ID function to find the id of the Person.Person table and then filter for it. This results in:

We now know there are seven foreign key constraints that are referencing columns on the Person.Person table.

Executing the sp_fkeys system stored procedure

Another way you can find all the foreign keys for a table is by using the sp_fkeys stored procedure.

There are a number of parameters you can pass through this stored procedure to filter the result set. For this demonstration we will only be using @pktable_owner and @pktable_name. For the others, check out the documentation here.

@pktable_owner is the schema that the target table belongs to.
@pktable_name is the name of the table that you are trying to find foreign keys on.

EXEC sp_fkeys @pktable_owner = 'Person', 
@pktable_name = 'Person';

The table returned is quite wide, lets zoom into it a bit.

We see that the same seven foreign key constraints are returned, just like from the first method.

Hope this helps if you ever need to find all foreign key constraints that are referencing a table. Leave a comment if you know any other methods!

Advertisement