Find Foreign Key Names created for SQL Server Database Table
SQL Server database administrator and T-SQL programmers can query sys.foreign_keys SQL system view to list and find foreign key check constraints on a database table. SQL Server system catalog view sys.foreign_keys is the right place for identifying and listing foreign keys and foreign key names created on a SQL database table or referencing to a specific SQL table. This SQL tutorial shows how to query sys.foreign_keys for desired result.
On SQL Server 2014 sample database AdventureWorks2014, when I execute SELECT command over system view sys.foreign_keys I got a huge list of foreign key constraints created on that database's tables. The foreign key name, object id, parent object id and the reference object id are some other important data about the foreign key constraint.
Use AdventureWorks2014
select * from sys.foreign_keys
If you want to search foreign keys created on a SQL table, you can use following SELECT command querying sys.foreign_keys system view on parent_object_id column value.
select * from sys.foreign_keys
where
parent_object_id = OBJECT_ID('ReferringTable') -- Referring SQL Table
and type = 'F' -- Check Constraint
On the other hand, if SQL programmer is dealing with SQL tables referring to a specific table, then sys.foreign_keys system view can be filtered according to referenced_object_id field value as follows.
select * from sys.foreign_keys
where
referenced_object_id = OBJECT_ID('ReferencedTable') -- Referenced SQL table
and type = 'F' -- Check Constraint
I modified the above queries in the following way to see explicitly the table names easily.
The result set shows the foreign key constraint name and the directing of the check constraint, from created database table to referenced SQL table.
select
name [foreign key constraint name],
OBJECT_NAME(parent_object_id) [created table],
OBJECT_NAME(referenced_object_id) [referenced table]
from sys.foreign_keys
where
parent_object_id = OBJECT_ID('HumanResources.Employee') OR
referenced_object_id = OBJECT_ID('HumanResources.Employee')
