MySQLで特定のカラム(columnA・columnB)を含むすべてのテーブルを検索する方法

MySQLデータベース内で、特定のカラム名(たとえば「columnA」と「columnB」)を両方とも含んでいるテーブルを一括で調べたい場面はよくあります。そんなときに便利なのが、システムテーブル information_schema.columns です。

この記事では、例として「columnA」を Id、「columnB」を Name に置き換えて、具体的な手順を解説します。

information_schema.columnsを使った検索クエリ

information_schema.columns には、データベース内のすべてのテーブルとカラムの情報が格納されています。このテーブルに対して GROUP BY と HAVING を組み合わせることで、「指定したカラムをすべて持つテーブル」だけを抽出できます。

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)

このように、条件を満たす12件のテーブルが抽出されました。

結果の検証方法

本当にこれらのテーブルが両方のカラムを持っているのか確認してみましょう。たとえば demotable233 の構造を DESC(DESCRIBE)コマンドでチェックします。

mysql> DESC demotable233;

実行すると、次のようなテーブル定義が表示されます。

+-------+-------------+------+-----+---------+----------------+
| 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)

ご覧のとおり、このテーブルには Id(int型・主キー・自動採番)と Name(varchar型)の2つのカラムが存在しており、検索結果が正しいことがわかります。

まとめ

• 特定のカラムを持つテーブルを探すには information_schema.columns を利用します。
• GROUP BY table_name と HAVING COUNT(*) = カラム数 を組み合わせることで、「複数のカラムをすべて含むテーブル」を正確に抽出できます。
• カラム数を変えれば、3つ以上のカラムを含むテーブルの検索にも応用可能です。