MySQLデータベースで特定のカラム名を含むテーブルを調べる方法

MySQLデータベース内に、ある特定のカラム（列）名を持つテーブルがいくつあるのか、またどのテーブルにそのカラムが含まれているのかを確認したい場面はよくあります。そんなときは、information_schema.columns テーブルに対して COLUMN_NAME を条件に検索を実行することで、簡単に調べることができます。

サンプルテーブルの作成

まずは動作確認用に、以下のようなテーブルを作成してみましょう。

mysql> create table DemoTable
-> (
-> CustomerId int,
-> CustomerName varchar(20),
-> CustomerCountryName varchar(100)
-> );
Query OK, 0 rows affected (1.05 sec)

特定のカラム名を含むテーブルを検索するクエリ

次に、「CustomerCountryName」というカラムがどのテーブルに存在するかを調べるクエリを見てみましょう。information_schema.columns を参照し、COLUMN_NAME で条件を絞り込みます。

mysql> select * from information_schema.columns WHERE COLUMN_NAME = 'CustomerCountryName';

実行結果

このクエリを実行すると、以下のような出力が得られます。

+---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME   | COLUMN_NAME         | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |
+---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+
| def           | sample       | demotable189 | CustomerCountryName | 4                | NULL           | YES         | varchar   |
| def           | web          | DemoTable    | CustomerCountryName | 4                | NULL           | YES         | varchar   |
| def           | web          | DemoTable    | CustomerCountryName | 3                | NULL           | YES         | varchar   |
+---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+
3 rows in set (0.68 sec)

結果の見方

実行結果から、「CustomerCountryName」というカラムは3つのテーブルに存在していることがわかります。特に注目すべきカラムは以下のとおりです。

• TABLE_SCHEMA： カラムが属するデータベース名（sample、web など）
• TABLE_NAME： カラムを含むテーブル名（demotable189、DemoTable など）
• ORDINAL_POSITION： テーブル内でのカラムの位置（何番目のカラムか）

必要な情報だけを絞り込むテクニック

SELECT * では多くの情報が表示されすぎて見づらい場合は、必要なカラムだけを指定すると結果が読みやすくなります。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.columns
WHERE COLUMN_NAME = 'CustomerCountryName';

さらに、特定のデータベース内だけを対象に検索したい場合は、TABLE_SCHEMA の条件も追加しましょう。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.columns
WHERE COLUMN_NAME = 'CustomerCountryName'
AND TABLE_SCHEMA = 'web';

このように information_schema.columns を活用すれば、大量のテーブルが存在するデータベースでも、目的のカラムがどこにあるのかを素早く特定できます。システム保守やデータベース調査の際にぜひ活用してください。