MySQLで特定の2つのカラムを含むすべてのテーブルを検索する方法

データベース内に多数のテーブルがあると、「特定のカラムを両方持っているテーブルはどれか？」を一度に調べたい場面があります。そんなときは、MySQLのシステムビュー information_schema.columns を参照すれば、たった1つのSQLクエリで解決できます。

ここでは、例として「Id」と「Name」という2つのカラムを両方含むテーブルを検索します。

特定の2つのカラムを含むテーブルを検索するSQL

mysql> SELECT table_name AS TableNameFromWebDatabase
    -> FROM information_schema.columns
    -> WHERE column_name IN ('Id', 'Name')
    -> GROUP BY table_name
    -> HAVING COUNT(*) = 2;

クエリのポイント

• WHERE column_name IN ('Id', 'Name')：カラム名が「Id」または「Name」である行だけを抽出します。
• GROUP BY table_name：テーブルごとにヒット件数を集計できるよう、テーブル名でグループ化します。
• HAVING COUNT(*) = 2：検索対象の2カラムが両方とも存在するテーブルだけに絞り込みます。探すカラムの数に合わせてこの値を変更してください。

実行結果

上記のクエリを実行すると、Id カラムと Name カラムの両方を備えたテーブルの一覧が次のように取得できます。

+--------------------------+
| TableNameFromWebDatabase |
+--------------------------+
| student                  |
| distinctdemo             |
| secondtable              |
| groupconcatenatedemo     |
| indemo                   |
| ifnulldemo               |
| demotable211             |
| demotable212             |
| demotable223             |
| demotable233             |
| demotable251             |
| demotable255             |
+--------------------------+
12 rows in set (0.25 sec)

結果の検証

本当に両方のカラムが存在するか、DESC 文でテーブル構造を確認してみましょう。

mysql> DESC demotable233;

実行結果は次のとおりです。Id カラムと Name カラムがきちんと存在していることがわかります。

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
|    Id | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

応用：対象のデータベースを限定するには

同一サーバー上に複数のデータベースがある場合は、WHERE 句に table_schema の条件を追加すると、特定のデータベースのみを対象に検索できます。

SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'your_database_name'
  AND column_name IN ('Id', 'Name')
GROUP BY table_name
HAVING COUNT(*) = 2;

この手法を覚えておけば、テーブル数の多い大規模なデータベースでも、目的のカラムを持つテーブルを素早く特定できます。