外部キー制約情報を取得するSQL文です。
※DBによってはテーブルId.が大文字だったり小文字だったりします
- MySQL ( MariaDB )
SELECT T0.ID FK_ID ,T0.FOR_NAME ,T0.FOR_COL_NAME ,'>>>' ,T0.REF_NAME ,T0.REF_COL_NAME FROM ( SELECT T1.ID ,T1.FOR_NAME ,T2.FOR_COL_NAME ,'0' REFREV ,T1.REF_NAME ,T2.REF_COL_NAME ,T1.TYPE ,T2.POS FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN T1 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS T2 ON T1.ID = T2.ID WHERE T1.FOR_NAME = '【DB Id.】/【テーブルId.】' UNION ALL SELECT T3.ID ,T3.FOR_NAME ,T4.FOR_COL_NAME ,'1' REFREV ,T3.REF_NAME ,T4.REF_COL_NAME ,T3.TYPE ,T4.POS FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN T3 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS T4 ON T3.ID = T4.ID WHERE T3.REF_NAME = '【DB Id.】/【テーブルId.】' ) T0 ORDER BY T0.REFREV ,T0.ID ,T0.POS -- ※ver.8.0以上の場合は上記SQLに以下修正をしてください -- INNODB_SYS_FOREIGN → INNODB_FOREIGN -- INNODB_SYS_FOREIGN_COLS → INNODB_FOREIGN_COLS - PostgreSQL
SELECT T0.FK_ID ,T0.TABLE_SCHEMA ,T0.TABLE_ID ,T0.COL_ID ,'>>>' REFREV ,T0.REF_TABLE_SCHEMA ,T0.REF_TABLE_ID ,T0.REF_COL_ID ,T0.DELETE_RULE ,T0.UPDATE_RULE FROM ( SELECT T1.CONSTRAINT_NAME FK_ID ,T1.CONSTRAINT_SCHEMA TABLE_SCHEMA ,T1.TABLE_NAME TABLE_ID ,T3.COLUMN_NAME COL_ID ,T4.CONSTRAINT_SCHEMA REF_TABLE_SCHEMA ,T4.TABLE_NAME REF_TABLE_ID ,T5.COLUMN_NAME REF_COL_ID ,T6.DELETE_RULE ,T6.UPDATE_RULE ,'0' REFREV ,T3.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T1 INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T2 ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME AND T1.CONSTRAINT_TYPE = 'FOREIGN KEY' INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE T3 ON T1.CONSTRAINT_NAME = T3.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS T4 ON T2.UNIQUE_CONSTRAINT_NAME = T4.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE T5 ON T4.CONSTRAINT_NAME = T5.CONSTRAINT_NAME AND T3.ORDINAL_POSITION = T5.ORDINAL_POSITION INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T6 ON T1.CONSTRAINT_NAME = T6.CONSTRAINT_NAME WHERE T1.TABLE_NAME = '【テーブルId.】' UNION ALL SELECT T7.CONSTRAINT_NAME FK_KD ,T7.CONSTRAINT_SCHEMA TABLE_SCHEMA ,T7.TABLE_NAME TABLE_ID ,T9.COLUMN_NAME COL_ID ,T10.CONSTRAINT_SCHEMA REF_TABLE_SCHEMA ,T10.TABLE_NAME REF_TABLE_ID ,T11.COLUMN_NAME REF_COL_ID ,T12.DELETE_RULE ,T12.UPDATE_RULE ,'1' REFREV ,T9.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T7 INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T8 ON T7.CONSTRAINT_NAME = T8.CONSTRAINT_NAME AND T7.CONSTRAINT_TYPE = 'FOREIGN KEY' INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE T9 ON T7.CONSTRAINT_NAME = T9.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS T10 ON T8.UNIQUE_CONSTRAINT_NAME = T10.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE T11 ON T10.CONSTRAINT_NAME = T11.CONSTRAINT_NAME AND T9.ORDINAL_POSITION = T11.ORDINAL_POSITION INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T12 ON T7.CONSTRAINT_NAME = T12.CONSTRAINT_NAME WHERE T10.TABLE_NAME = '【テーブルId.】' ) T0 ORDER BY T0.REFREV ,T0.FK_ID ,T0.ORDINAL_POSITION - Oracle
SELECT T0.CONSTRAINT_NAME FK_ID ,T0.TABLE_NAME ,T0.COLUMN_NAME ,'>>>' REFREV ,T0.REF_TABLE_ID ,T0.REF_COL_ID ,T0.DELETE_RULE FROM ( SELECT T1.CONSTRAINT_NAME ,T1.TABLE_NAME ,T2.COLUMN_NAME ,T3.TABLE_NAME REF_TABLE_ID ,T4.COLUMN_NAME REF_COL_ID ,T1.DELETE_RULE ,'0' REFREV ,T2.POSITION FROM USER_CONSTRAINTS T1 INNER JOIN USER_CONS_COLUMNS T2 ON T1.TABLE_NAME = T2.TABLE_NAME AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME AND T1.CONSTRAINT_TYPE = 'R' INNER JOIN USER_CONSTRAINTS T3 ON T1.R_CONSTRAINT_NAME = T3.CONSTRAINT_NAME INNER JOIN USER_CONS_COLUMNS T4 ON T3.TABLE_NAME = T4.TABLE_NAME AND T3.CONSTRAINT_NAME = T4.CONSTRAINT_NAME AND T2.POSITION = T4.POSITION WHERE T1.OWNER = '【スキーマ(ユーザー)Id.】' AND T1.TABLE_NAME = '【テーブルId.】' UNION ALL SELECT T5.CONSTRAINT_NAME ,T5.TABLE_NAME ,T6.COLUMN_NAME ,T7.TABLE_NAME REF_TABLE_ID ,T8.COLUMN_NAME REF_COL_ID ,T5.DELETE_RULE ,'1' REFREV ,T6.POSITION FROM USER_CONSTRAINTS T5 INNER JOIN USER_CONS_COLUMNS T6 ON T5.TABLE_NAME = T6.TABLE_NAME AND T5.CONSTRAINT_NAME = T6.CONSTRAINT_NAME AND T5.CONSTRAINT_TYPE = 'R' INNER JOIN USER_CONSTRAINTS T7 ON T5.R_CONSTRAINT_NAME = T7.CONSTRAINT_NAME INNER JOIN USER_CONS_COLUMNS T8 ON T7.TABLE_NAME = T8.TABLE_NAME AND T7.CONSTRAINT_NAME = T8.CONSTRAINT_NAME AND T6.POSITION = T8.POSITION WHERE T7.OWNER = '【スキーマ(ユーザー)Id.】' AND T7.TABLE_NAME = '【テーブルId.】' ) T0 ORDER BY T0.REFREV ,T0.CONSTRAINT_NAME ,T0.POSITION - SQL Server
select T0.FK_ID ,T0.TABLE_ID ,T0.COL_ID ,'>>>' REFREV ,T0.REF_TABLE_ID ,T0.REF_COL_ID ,T0.DELETE_RULE ,T0.UPDATE_RULE from ( select T1.name FK_ID ,T3.name TABLE_ID ,T4.name COL_ID ,T5.name REF_TABLE_ID ,T6.name REF_COL_ID ,T1.delete_referential_action_desc DELETE_RULE ,T1.update_referential_action_desc UPDATE_RULE ,'0' REFREV ,T6.column_id COLUMN_ID from sys.foreign_keys T1 inner join sys.foreign_key_columns T2 on T1.object_id = T2.constraint_object_id inner join sys.tables T3 on T2.parent_object_id = T3.object_id inner join sys.columns T4 on T2.parent_object_id = T4.object_id and T2.parent_column_id = T4.column_id inner join sys.tables T5 on T2.referenced_object_id = T5.object_id inner join sys.columns T6 on T2.referenced_object_id = T6.object_id and T2.referenced_column_id = T6.column_id where T3.name = '【テーブルId.】' union all select T7.name FK_ID ,T9.name TABLE_ID ,T10.name COL_ID ,T11.name REF_TABLE_ID ,T12.name REF_COL_ID ,T7.delete_referential_action_desc DELETE_RULE ,T7.update_referential_action_desc UPDATE_RULE ,'1' REFREV ,T12.column_id COLUMN_ID from sys.foreign_keys T7 inner join sys.foreign_key_columns T8 on T7.object_id = T8.constraint_object_id inner join sys.tables T9 on T8.parent_object_id = T9.object_id inner join sys.columns T10 on T8.parent_object_id = T10.object_id and T8.parent_column_id = T10.column_id inner join sys.tables T11 on T8.referenced_object_id = T11.object_id inner join sys.columns T12 on T8.referenced_object_id = T12.object_id and T8.referenced_column_id = T12.column_id where T11.name = '【テーブルId.】' ) T0 order by T0.REFREV ,T0.FK_ID ,T0.COLUMN_ID