如何使用Information_Schema在SQL Server中尋找外鍵引用？

使用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，且 T_ALV_Ref_FilterDomeris_Ref_FilterDomer_AP_FIDS.要尋找引用的表和列，我們可以執行查詢：

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中文網其他相關文章！