How to find foreign key references in SQL Server: Just run this one query!

SQL Server foreign key constraints are one of the most useful tools available to us for maintaining the integrity of our data.

If you want to find all the foreign key references in your database, there is a very simple query you can run.

Just query the sys.foreign_keys and sys.foreign_key_columns system tables!

Everything we need to know about foreign key constrains can be found in the sys.foreign_keys and sys.foreign_key_columns system tables. We can write a JOIN between these two tables to give us all the details for all the foreign key constraints in our database.

Here’s the query:

SELECT F.NAME as 'Foreign key constraint name',
OBJECT_NAME(F.parent_object_id) AS 'Referencing/Child Table',
COL_NAME(FC.parent_object_id, FC.parent_column_id) AS 'Referencing/Child Column',
OBJECT_NAME(FC.referenced_object_id) AS 'Referenced/Parent Table',
COL_NAME(FC.referenced_object_id, FC.referenced_column_id) AS 'Referenced/Parent Column'
FROM sys.foreign_keys AS F
INNER JOIN sys.foreign_key_columns AS FC
ON F.OBJECT_ID = FC.constraint_object_id
--WHERE OBJECT_NAME (F.referenced_object_id) = 'table_name'
ORDER BY OBJECT_NAME(F.parent_object_id)

Notice the WHERE clause that is commented out. If you wanted to find the foreign key constraints for a specific table, you would uncomment out that line and replace ‘table_name’ with the name of the table.

For now, we’ll leave that WHERE clause commented out so that we can see all foreign key constraints for all tables in our database.

An example of finding foreign key references

Take a look at the following Orders table:

The CustID column is a foreign link to the Customers table to tell us who placed the order. The ProdID column is a foreign link to the Products table to tell us what product was purchased.

Both of these columns have a foreign key constraint to ensure data integrity. We can see these constraints in the Object Explorer of SQL Server Management Studio by navigating to the table and expanding the Keys folder:

We can look at the details of one of these constraints by writing it out to a new query editor window like so:

This opens a new window with the following details:

We can see this foreign key constraint is placed on the CustID column of the Orders table, and it references the CustID column of the Customers table. Fairly simple.

Running our query is easier

We can find foreign key references by using the Object Explorer as demonstrated, but it might be more simple to just run our query instead.

Here’s an example:

Here’s a breakdown of the columns returned from this query:

• Foreign key constraint name – This is the name of the foreign key constraint.
• Referencing/Child Table – The referencing table, sometimes called the child table.
• Referencing/Child Column – The referencing column, sometimes called the child column.
• Referenced/Parent Table – The referenced table, sometimes called the parent table.
• Referenced/Parent Column – The referenced column, sometimes called the parent column. This is the column from which the values originate. The referencing column can only contain numbers that exist in this column.

And remember, if you want to filter your results to a specific table, just uncomment out the WHERE clause and replace ‘table_name’ with your table name.

Next Steps:

Leave a comment if you found this tutorial helpful!

Make sure you check out the full tutorial on foreign key constraints:

SQL Server Foreign Key: Everything you need to know 

Also make sure you download your FREE Ebook:

FREE Ebook on SQL Server Constraints!

This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:

• Primary Key constraints
• Foreign Key constraints
• Default constraints
• Check constraints
• Unique constraints

Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!