Note
Access to this page requires authorization. You can try signing in or .
Access to this page requires authorization. You can try .
sys.foreign_key_columns (Transact-SQL)
Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Database 👁 Image
Azure SQL Managed Instance 👁 Image
Azure Synapse Analytics 👁 Image
Analytics Platform System (PDW) 👁 Image
Warehouse in Microsoft Fabric 👁 Image
SQL database in Microsoft Fabric
Contains a row for each column, or set of columns, that comprise a foreign key.
| Column name | Data type | Description |
|---|---|---|
| constraint_object_id | int | ID of the FOREIGN KEY constraint. |
| constraint_column_id | int | ID of the column, or set of columns, that comprise the FOREIGN KEY (1..n where n is the number of columns). |
| parent_object_id | int | ID of the parent of the constraint, which is the referencing object. |
| parent_column_id | int | ID of the parent column, which is the referencing column. |
| referenced_object_id | int | ID of the referenced object, which has the candidate key. |
| referenced_column_id | int | ID of the referenced column (candidate key column). |
Permissions
The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration.
Example query
The following Transact-SQL query retrieves all foreign keys in the database, including their related tables and columns.
SELECT fk.name AS ForeignKeyName
, t_parent.name AS ParentTableName
, c_parent.name AS ParentColumnName
, t_child.name AS ReferencedTableName
, c_child.name AS ReferencedColumnName
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables t_parent
ON t_parent.object_id = fk.parent_object_id
INNER JOIN sys.columns c_parent
ON fkc.parent_column_id = c_parent.column_id
AND c_parent.object_id = t_parent.object_id
INNER JOIN sys.tables t_child
ON t_child.object_id = fk.referenced_object_id
INNER JOIN sys.columns c_child
ON c_child.object_id = t_child.object_id
AND fkc.referenced_column_id = c_child.column_id
ORDER BY t_parent.name, c_parent.name;
See also
Feedback
Was this page helpful?