カテゴリー: WEB制作

MySQLデータベースで外部キーを持つテーブルの一覧を出すクエリのメモ。

SELECT F1.table_schema    AS TABLE_SCHEMA, 
       F1.table_name      AS TABLE_NAME, 
       F1.column_name     AS COLUMN_NAME, 
       F2.constraint_type AS CONSTRAINT_TYPE, 
       F2.constraint_name AS CONSTRAINT_NAME 
FROM   information_schema.key_column_usage F1 
       LEFT JOIN information_schema.table_constraints F2 
              ON F1.table_schema = F2.table_schema 
                 AND F1.constraint_name = F2.constraint_name 
WHERE  F2.constraint_type = 'FOREIGN KEY'