MySQLでデータベース内のすべてのテーブルから特定のカラムを検索する方法

はじめに

データベースを運用していると、「特定のカラムがどのテーブルに存在するのか」を把握したくなる場面があります。特に大規模なデータベースでは、同名のカラムが複数のテーブルに散らばっていることも珍しくありません。

そんなときに役立つのが、MySQLの INFORMATION_SCHEMA.COLUMNS テーブルです。この情報スキーマを利用することで、データベース内のすべてのテーブルから目的のカラムを一括して検索できます。

基本となる検索方法

カラム名を検索するには、COLUMN_NAME を対象に LIKE 句で条件を指定します。さらに table_schema にデータベース名を指定することで、検索範囲を限定できます。

以下は、「web」データベース内の全テーブルから「StudentName」というカラムを持つテーブルを検索する例です。

mysql> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE column_name LIKE '%StudentName%'
    -> AND table_schema = 'web';

実行結果

上記のクエリを実行すると、次のような結果が得られます。

+-------------------+-------------+-----------+-------------+----------------+
| TABLE_NAME        | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |COLUMN_DEFAULT  |
+-------------------+-------------+-----------+-------------+----------------+
| demotable215      | StudentName | varchar   | YES         | NULL           |
| demotable221      | StudentName | varchar   | YES         | NULL           |
| demotable224      | StudentName | varchar   | YES         | NULL           |
| demotable234      | StudentName | varchar   | YES         | NULL           |
| demotable269      | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | YES         | NULL           |
| DemoTable         | StudentName | varchar   | NO          | NULL           |
| DemoTable         | StudentName | json      | YES         | NULL           |
| view_DemoTable    | StudentName | varchar   | YES         | NULL           |
+-------------------+-------------+-----------+-------------+----------------+

この結果から、「StudentName」というカラムを持つすべてのテーブルとビューが一覧表示されました。各テーブルにおけるカラムのデータ型（DATA_TYPE）やNULL許可の有無（IS_NULLABLE）も同時に確認できるのがポイントです。

取得できる主な情報

• TABLE_NAME： カラムが属するテーブル名
• COLUMN_NAME： カラム名
• DATA_TYPE： データ型（varchar、jsonなど）
• IS_NULLABLE： NULL値を許可するかどうか（YES / NO）
• COLUMN_DEFAULT： カラムのデフォルト値

応用テクニック

部分一致ではなく完全一致で検索する

より厳密に検索したい場合は、LIKE '%StudentName%' の代わりに = 'StudentName' を使用すると完全一致になります。

あいまい検索を活用する

逆に、カラム名の一部だけしかわからない場合も、LIKE '%name%' のようにワイルドカードを使えば柔軟に検索できます。たとえば「email」に関連するカラムを探すなら LIKE '%mail%' と指定します。

すべてのデータベースを対象にする

table_schema = 'web' の条件を外せば、サーバー上のすべてのデータベースが検索対象になります。ただし結果が大量になる可能性があるため、必要に応じて条件を組み合わせて絞り込みましょう。

まとめ

INFORMATION_SCHEMA.COLUMNSを活用すれば、どのテーブルに定義されているかわからないカラムでも、SQL一発で簡単に特定できます。データベースの保守やリファクタリング、影響範囲の調査など、実務で非常に役立つテクニックなので、ぜひ覚えておきましょう。