SQL Server : Howto get foreign key reference from information_schema?

Question

在SQL Server中，如何从外键中获取引用的表+列名？

注意:不是键所在的表/列，而是它引用的键。

示例:

当表[T_ALV_Ref_FilterDisplay]中的键[FA_MDT_ID]时。指[T_AP_Ref_Customer].[MDT_ID]

例如创建这样的约束时:

ALTER TABLE [dbo].[T_ALV_Ref_FilterDisplay]  WITH CHECK ADD  CONSTRAINT [FK_T_ALV_Ref_FilterDisplay_T_AP_Ref_Customer] FOREIGN KEY([FA_MDT_ID])
REFERENCES [dbo].[T_AP_Ref_Customer] ([MDT_ID])
GO

我需要获取[T_AP_Ref_Customer].[MDT_ID]当给定 [T_ALV_Ref_FilterAnzeige].[FA_MDT_ID] 作为输入时

Answer 1

没关系，这是正确的答案:
http://msdn.microsoft.com/en-us/library/aa175805(SQL.80).aspx

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 

注意:
Information_schema 不包含索引(它确实找到唯一约束)。
因此，如果您想根据唯一索引查找外键，则需要查看微软专有表:

SELECT  
     fksch.name AS FK_CONSTRAINT_SCHEMA 
    ,fk.name AS FK_CONSTRAINT_NAME 

    ,sch1.name AS FK_TABLE_SCHEMA 
    ,t1.name AS FK_TABLE_NAME 
    ,c1.name AS FK_COLUMN_NAME 
    -- The column_id is not the ordinal, it can be dropped and then there's a gap... 
    ,COLUMNPROPERTY(c1.object_id, c1.name, 'ordinal') AS FK_ORDINAL_POSITION 

    ,COALESCE(pksch.name,sch2.name) AS REFERENCED_CONSTRAINT_SCHEMA 
    ,COALESCE(pk.name, sysi.name) AS REFERENCED_CONSTRAINT_NAME 

    ,sch2.name AS REFERENCED_TABLE_SCHEMA 
    ,t2.name AS REFERENCED_TABLE_NAME 
    ,c2.name AS REFERENCED_COLUMN_NAME 
    ,COLUMNPROPERTY(c2.object_id, c2.name, 'ordinal') AS REFERENCED_ORDINAL_POSITION 
FROM sys.foreign_keys AS fk 

LEFT JOIN sys.schemas AS fksch 
    ON fksch.schema_id = fk.schema_id 

-- not inner join: unique indices 
LEFT JOIN sys.key_constraints AS pk
    ON pk.parent_object_id = fk.referenced_object_id 
    AND pk.unique_index_id = fk.key_index_id 

LEFT JOIN sys.schemas AS pksch 
    ON pksch.schema_id = pk.schema_id 

LEFT JOIN sys.indexes AS sysi 
    ON sysi.object_id = fk.referenced_object_id 
    AND sysi.index_id = fk.key_index_id 

INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.constraint_object_id = fk.object_id 

INNER JOIN sys.tables AS t1 
    ON t1.object_id = fkc.parent_object_id 

INNER JOIN sys.schemas AS sch1 
    ON sch1.schema_id = t1.schema_id 

INNER JOIN sys.columns AS c1 
    ON c1.column_id = fkc.parent_column_id 
    AND c1.object_id = fkc.parent_object_id 

INNER JOIN sys.tables AS t2 
    ON t2.object_id = fkc.referenced_object_id 

INNER JOIN sys.schemas AS sch2 
    ON sch2.schema_id = t2.schema_id 

INNER JOIN sys.columns AS c2 
    ON c2.column_id = fkc.referenced_column_id 
    AND c2.object_id = fkc.referenced_object_id

边缘情况的验证测试:

CREATE TABLE __groups ( grp_id int, grp_name varchar(50), grp_name2 varchar(50) )
ALTER TABLE __groups ADD CONSTRAINT UQ___groups_grp_name2 UNIQUE (grp_name2)
CREATE UNIQUE INDEX IX___groups_grp_name ON __groups(grp_name)

GO
CREATE TABLE __group_mappings( map_id int, map_grp_name varchar(50), map_grp_name2 varchar(50), map_usr_name varchar(50) )
GO

ALTER TABLE __group_mappings  ADD  CONSTRAINT FK___group_mappings___groups FOREIGN KEY(map_grp_name)
REFERENCES __groups (grp_name)
GO


ALTER TABLE __group_mappings  ADD  CONSTRAINT FK___group_mappings___groups2 FOREIGN KEY(map_grp_name2)
REFERENCES __groups (grp_name2)
GO


SELECT @@VERSION -- Microsoft SQL Server 2016 (SP1-GDR) (KB4458842)
SELECT version() -- PostgreSQL 9.6.6 on x86_64-pc-linux-gnu
GO