如何在 SQL Server 2008 中獲取表的外鍵和主鍵詳細資訊

我想使用 2 個查詢，一個列出具有以下資訊的給定表的所有外鍵，Schema name, foreign table name另一個列出所有主鍵。

這個列出主鍵的查詢是否正確：

select syssc.name as schemaname , cast(c.name as  varchar(255)) as foreign_table , 
cast(p.name as varchar(255)) as primary_table 
from sysobjects f 
inner join sysobjects c on  f.parent_obj = c.id 
inner join sysreferences r on f.id =  r.constid 
inner join sysobjects p on r.rkeyid = p.id 
inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid 
inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid 
left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid 
left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid 
inner join sys.tables syst on rc.id=syst.object_id 
inner join sys.schemas syssc on syst.schema_id=syssc.schema_id 
where  f.type =  'F' AND c.name in ('table name' )

這是我現在用來獲取外鍵的查詢

select syssc.name as schemaname ,cast(c.name as  varchar(255)) as foreign_table ,  
cast(p.name as varchar(255)) as primary_table 
from sysobjects f 
inner join sysobjects c on  f.parent_obj = c.id 
inner join sysreferences r on f.id =  r.constid 
inner join sysobjects p on r.rkeyid = p.id 
inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid 
inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid 
left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid 
left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid 
inner join sys.tables syst on rc.id=syst.object_id 
inner join sys.schemas syssc on syst.schema_id=syssc.schema_id 
where  f.type =  'F' and p.name in ('table name')

請檢查這些查詢是否正確和有效。

您應該熟悉新的目錄視圖。舊的 sysobjects、syscolumns 等已被棄用，僅出於向後兼容的原因提供 - 它們不應用於新開發，舊程式碼最終應轉換為新對象。

這是一個查詢，它將獲取外鍵所涉及的列的兩側：

SELECT 
       constraint_name   = OBJECT_NAME(fkc.constraint_object_id), 
       foreign_schema    = SCHEMA_NAME(child.[schema_id]), 
       foreign_table     = child.name, 
       foreign_column    = child_cols.name, 
       referenced_schema = SCHEMA_NAME(referenced.[schema_id]),
       referenced_table  = referenced.name, 
       referenced_column = referenced_cols.name
   FROM sys.foreign_key_columns AS fkc
   INNER JOIN sys.tables AS child
   ON fkc.parent_object_id = child.[object_id]
   INNER JOIN sys.tables AS referenced
   ON fkc.referenced_object_id = referenced.[object_id]
   INNER JOIN sys.columns AS referenced_cols
   ON fkc.parent_column_id = referenced_cols.column_id
   AND referenced_cols.[object_id] = referenced.[object_id]
   INNER JOIN sys.columns AS child_cols
   ON fkc.referenced_column_id = child_cols.column_id
   AND child_cols.[object_id] = child.[object_id]
   WHERE referenced.name = 'table name'
   -- if you want the tables that a child table references, use this instead:
   -- WHERE child.name = 'table name'
   ORDER BY fkc.parent_column_id;

而這個要簡單得多，僅針對所涉及的表：

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) = 'table name'
-- if you want the tables that a child table references, use this instead:
-- WHERE OBJECT_NAME(parent_object_id) = 'table name';

編輯添加一些文件連結（這些連結大多屬於 SQL Server 2008 R2 層次結構，但它們也大致等同於 SQL Server 2008）：

• 目錄視圖
• 查詢目錄視圖
• 查詢目錄視圖 FAQ
• 元數據函式

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