How do I find all the foreign keys in a database? 如何找到一个数据库中的全部外键(FK)?

    技术2022-05-11  15


    SQL Server    Here is one query using the INFORMATION_SCHEMA views that returns both sides of all FOREIGN KEY relationships, as well as the name of the foreign key constraint.    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     INNER JOIN     INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK         ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME     INNER JOIN     INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK         ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME     INNER JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU         ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME     INNER JOIN     (         SELECT             i1.TABLE_NAME, i2.COLUMN_NAME         FROM             INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1             INNER 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 -- optional: ORDER BY     1,2,3,4   If you want to limit it to specific tables, you can add any of the following immediately prior to the optional ORDER BY clause:        WHERE PK.TABLE_NAME='something'      WHERE FK.TABLE_NAME='something'      WHERE PK.TABLE_NAME IN ('one_thing', 'another')      WHERE FK.TABLE_NAME IN ('one_thing', 'another')