如何查找具有表 A 主键外键约束的表列表？

Question

我如何快速找到对特定表的主键具有外键约束的各种表的列表。

请指导...

Answer 1

这是使用 information_schema 执行此操作的查询，改编自 此博文：

SELECT  FK_Table = FK.TABLE_NAME
,       FK_Column = CU.COLUMN_NAME
,       PK_Table = PK.TABLE_NAME
,       PK_Column = PT.COLUMN_NAME
,       Constraint_Name = C.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
JOIN    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
ON      C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
ON      C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
JOIN    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
ON      C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
JOIN    (
        SELECT  i1.TABLE_NAME, i2.COLUMN_NAME
        FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
        JOIN    INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
        ON      i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE   i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
        ) PT 
ON      PT.TABLE_NAME = PK.TABLE_NAME
WHERE   PK.TABLE_NAME = 'PrimaryKeyTable'