如何使用Information_Schema在SQL Server中查找外键引用？
- Linda Hamilton原创
- 2025-01-02 17:20:40819浏览
使用 SQL Server 的信息架构获取外键引用
在 SQL Server 中，识别外键引用的表和列可能是有价值的信息用于理解数据关系。要获取此信息，我们可以利用 information_schema 视图。
SELECT
KCU1.CONSTRAINT_SCHEMA AS FK_CONSTRAINT_SCHEMA
,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
,KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA
,KCU1.TABLE_NAME AS FK_TABLE_NAME
,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
,KCU2.CONSTRAINT_SCHEMA AS REFERENCED_CONSTRAINT_SCHEMA
,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA
,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
示例：
假设我们有表 T_ALV_Ref_FilterDisplay 和 T_AP_Ref_Customer，并且 T_ALV_Ref_FilterDisplay 有外键约束参考T_AP_Ref_Customer.MDT_ID。要查找引用的表和列，我们可以执行查询：
SELECT
REFERENCED_TABLE_SCHEMA,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE
FK_TABLE_SCHEMA = 'dbo'
AND FK_TABLE_NAME = 'T_ALV_Ref_FilterDisplay'
AND FK_COLUMN_NAME = 'FA_MDT_ID';
这将返回以下结果：
REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME ------------------------ | ---------------------- | ---------------------- dbo | T_AP_Ref_Customer | MDT_ID
限制：
请注意，information_schema 不包含有关索引的信息。如果外键引用基于唯一索引，则需要使用 Microsoft 专有表。
以上是如何使用Information_Schema在SQL Server中查找外键引用？的详细内容。更多信息请关注PHP中文网其他相关文章！
声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn