如何从SQL Server中的sys表中识别外键?

Question

我使用以下查询返回两个表的主键。是否有类似的方法来查询外键？我不熟悉创建数据库，但我没有看到在 SSMS 的对象资源管理器中定义任何外键。谢谢

select schema_name(tab.schema_id) as [schema_name], 
pk.[name] as pk_name,
ic.index_column_id as column_id,
col.[name] as column_name, 
tab.[name] as table_name
from sys.tables tab
inner join sys.indexes pk
on tab.object_id = pk.object_id 
and pk.is_primary_key = 1
inner join sys.index_columns ic
on ic.object_id = pk.object_id
and ic.index_id = pk.index_id
inner join sys.columns col
on pk.object_id = col.object_id
and col.column_id = ic.column_id
where tab.name = 'custtable' or tab.name = 'custtrans'
order by schema_name(tab.schema_id),
pk.[name],
ic.index_column_id

这是输出，但我需要它来返回这两个表的外键。

schema_name     pk_name     column_id       column_name    table_name
dbo         I_077ACCOUNTIDX         1        ACCOUNTNUM     CUSTTABLE
dbo         I_077ACCOUNTIDX         2        DATAAREAID     CUSTTABLE
dbo         I_077ACCOUNTIDX         3        PARTITION      CUSTTABLE
dbo         I_078RECID              1        RECID          CUSTTRANS

Answer 1

这对你有用吗？ (这通过将多键引用显示为逗号分隔的列列表来处理多键引用，仅供参考(：

SELECT fkeys.[name] AS FKName, 
OBJECT_NAME(fkeys.parent_object_id) AS TableName,
(SELECT STUFF((SELECT ',' + c.[name]
FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
INNER JOIN sys.columns as c ON t.object_id = c.object_id
INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.parent_column_id 
AND fc.constraint_object_id = fk.object_id 
AND fc.parent_object_id = fk.parent_object_id 
WHERE fk.[name] = fkeys.[name]
FOR XML PATH ('')), 1, 1, '')) AS FKFolumns,
OBJECT_NAME(fkeys.referenced_object_id) AS ReferencedTableName,
(SELECT STUFF((SELECT ',' + c.[name]
FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.referenced_object_id = t.object_id
INNER JOIN sys.columns as c ON t.object_id = c.object_id
INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.referenced_column_id 
AND fc.constraint_object_id = fk.object_id 
AND fc.referenced_object_id = fk.referenced_object_id 
WHERE fk.[name] = fkeys.[name]
FOR XML PATH ('')), 1, 1, '')) AS ReferencedFKFolumns
FROM sys.foreign_keys fkeys
ORDER BY FKName;