如何查找表a主键有外键禁忌的表列表?

How can i quickly find out the list of various tables that are having foreign key constraint to the primary key of a particular table.

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

Kindly guide...

请指导……

1 个解决方案

#1

Here's a query to do that using information_schema, adapted from this blog post:

这里有一个使用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'