sys.foreign_key_columns (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Warehouse 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
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho