SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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

sys.foreign_keys SQL Server system catalog view for foreign key constraints

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
 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
 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.

 name [foreign key constraint name],
 OBJECT_NAME(parent_object_id) [created table],
 OBJECT_NAME(referenced_object_id) [referenced table]
from sys.foreign_keys
 parent_object_id = OBJECT_ID('HumanResources.Employee') OR
 referenced_object_id = OBJECT_ID('HumanResources.Employee')

query foreign keys created for a specific SQL Server database table

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.