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 SQL Server Tools Reviews and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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
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')

query foreign keys created for a specific SQL Server database table






Related SQL Resources

SQL Server Articles

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server Tools

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems