查找與給定主鍵關聯的外鍵

我想要一種方法來確定給定數據庫中的哪些列通過 PK/FK 關係連接。我可以通過返回給定表的 PK/FK 資訊

SELECT *  
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu 
WHERE EXISTS (
   SELECT tc.* 
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
   WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'  
       AND tc.TABLE_NAME = 'MyTable'  
       /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
       AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO

但是對於從這樣的查詢返回的 PK，我如何建立關聯的 FK（假設有一個）？

我知道您還可以通過以下方式獲取引用的表：

SELECT CONSTRAINT_NAME = name, 
      FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id), 
      FOREIGN_TABLE = OBJECT_NAME(parent_object_id), 
      REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id), 
      REFERENCED_TABLE = OBJECT_NAME(referenced_object_id) 
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO

但我現在正在努力獲得明確的列引用。

我正在為 QlikView 創建一個腳本生成器。要生成腳本，我需要約束和相關連結。我需要任何給定列（如果有）的所有約束資訊。

我想建構一個數據庫類來保存給定數據庫的所有資訊。然後，這個類結構database.table.column.constraints將用於獲取 PK/FK 上不同列之間的匹配。

顯然有些列只有 FK，在這種情況下，我還想檢索相應鍵的 PK 資訊；有些人只有PK，然後我想要相反。有些當然可以兩者兼得。

這是一個簡單的查詢，用於將外鍵與其引用的表/列匹配：

SELECT
   o1.name AS FK_table,
   c1.name AS FK_column,
   fk.name AS FK_name,
   o2.name AS PK_table,
   c2.name AS PK_column,
   pk.name AS PK_name,
   fk.delete_referential_action_desc AS Delete_Action,
   fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
   INNER JOIN sys.foreign_keys fk
       ON o1.object_id = fk.parent_object_id
   INNER JOIN sys.foreign_key_columns fkc
       ON fk.object_id = fkc.constraint_object_id
   INNER JOIN sys.columns c1
       ON fkc.parent_object_id = c1.object_id
       AND fkc.parent_column_id = c1.column_id
   INNER JOIN sys.columns c2
       ON fkc.referenced_object_id = c2.object_id
       AND fkc.referenced_column_id = c2.column_id
   INNER JOIN sys.objects o2
       ON fk.referenced_object_id = o2.object_id
   INNER JOIN sys.key_constraints pk
       ON fk.referenced_object_id = pk.parent_object_id
       AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id

輸出有八列：外鍵的表名和列名（FK_table、FK_column）、外鍵約束的名稱（FK_name）、引用的 PK 或唯一索引表和列名（PK_table、PK_column）、引用的 PK 或唯一索引 (PK_name) 的名稱，以及更新/刪除級聯操作 (Delete_Action、Update_Action)。

（編輯以添加更多輸出列。）

編輯： 6 年後我帶著改進版回來了。我意識到原來的查詢並不能很好地處理多列外鍵，而且我還希望能夠快速辨識禁用、不受信任或未索引的外鍵。所以這是糾正所有這些的新版本。

多列鍵在 和 中顯示為逗號分隔的列表FK_columns，PK_columns使用傳統的FOR XML/STUFF濫用。該FK_indexes列顯示了外鍵表上的任何索引的名稱，這些索引可能用於滿足使用外鍵列的搜尋（主要用於優化對主鍵表的刪除或更新）。如果是NULL，那麼你有一個未索引的外鍵。如果您想按 PK 表名稱排序、過濾特定的 PK/FK 表等，您可以調整ORDER BY, 或添加一個WHERE子句（在下面註釋掉）。

SELECT
   fk.is_disabled,
   fk.is_not_trusted,
   OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
   o1.name AS FK_table,
   --Generate list of columns in referring side of foreign key
   STUFF(
       (
           SELECT ', ' + c1.name AS [text()]
           FROM sys.columns c1 INNER
               JOIN sys.foreign_key_columns fkc
                   ON c1.object_id = fkc.parent_object_id
                   AND c1.column_id = fkc.parent_column_id
           WHERE fkc.constraint_object_id = fk.object_id
           FOR XML PATH('')
       ), 1, 2, '') AS FK_columns,
   --Look for any indexes that will fully satisfy the foreign key columns
   STUFF(
       (
           SELECT ', ' + i.name AS [text()]
           FROM sys.indexes i
           WHERE i.object_id = o1.object_id
               AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
                   SELECT fkc.constraint_column_id, fkc.parent_column_id
                   FROM sys.foreign_key_columns fkc
                   WHERE fkc.constraint_object_id = fk.object_id
                   EXCEPT
                   SELECT ic.key_ordinal, ic.column_id
                   FROM sys.index_columns ic
                   WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
               )
           FOR XML PATH('')
       ), 1, 2, '') AS FK_indexes,
   fk.name AS FK_name,
   OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
   o2.name AS PK_table,
   --Generate list of columns in referenced (i.e. PK) side of foreign key
   STUFF(
       (
           SELECT ', ' + c2.name AS [text()]
           FROM sys.columns c2
               INNER JOIN sys.foreign_key_columns fkc
                   ON c2.object_id = fkc.referenced_object_id
                   AND c2.column_id = fkc.referenced_column_id
           WHERE fkc.constraint_object_id = fk.object_id
           FOR XML PATH('')
       ), 1, 2, '') AS PK_columns,
   pk.name AS PK_name,
   fk.delete_referential_action_desc AS Delete_Action,
   fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
   INNER JOIN sys.foreign_keys fk
       ON o1.object_id = fk.parent_object_id
   INNER JOIN sys.objects o2
       ON fk.referenced_object_id = o2.object_id
   INNER JOIN sys.key_constraints pk
       ON fk.referenced_object_id = pk.parent_object_id
       AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name

引用自：https://dba.stackexchange.com/questions/31720