ストレージエンジンを指定してMySQLのすべてのテーブルを一覧表示する方法

INFORMATION_SCHEMA.TABLESでストレージエンジン別にテーブルを確認する

MySQLでは、INFORMATION_SCHEMA.TABLES ビューに対して WHERE 句でストレージエンジンを指定することで、そのエンジンを使用しているすべてのテーブルを簡単に一覧表示できます。ここでは、最も一般的に使われている「InnoDB」を例に、具体的な手順を解説します。

基本構文

特定のストレージエンジンを持つテーブル名だけを抽出したい場合は、次のような構文を使用します。

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'InnoDB';

実行例1：テーブルの詳細情報まで取得する

SELECT * を使うと、テーブル名だけでなく、スキーマ名・行数・作成日時・照合順序などの詳細情報も併せて取得できます。

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'InnoDB';

実行結果は非常に長くなるため、以下に主要カラムを抜粋して示します。

+---------------+--------------+---------------------------+------------+--------+---------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                | TABLE_TYPE | ENGINE | VERSION |
+---------------+--------------+---------------------------+------------+--------+---------+
| def           | mysql        | innodb_table_stats        | BASE TABLE | InnoDB |      10 |
| def           | mysql        | innodb_index_stats        | BASE TABLE | InnoDB |      10 |
| def           | mysql        | db                        | BASE TABLE | InnoDB |      10 |
| def           | mysql        | user                      | BASE TABLE | InnoDB |      10 |
| def           | mysql        | default_roles             | BASE TABLE | InnoDB |      10 |
| def           | mysql        | role_edges                | BASE TABLE | InnoDB |      10 |
| def           | mysql        | global_grants             | BASE TABLE | InnoDB |      10 |
| def           | mysql        | password_history          | BASE TABLE | InnoDB |      10 |
| def           | mysql        | tables_priv               | BASE TABLE | InnoDB |      10 |
| def           | mysql        | columns_priv              | BASE TABLE | InnoDB |      10 |
| def           | mysql        | help_topic                | BASE TABLE | InnoDB |      10 |
| def           | mysql        | time_zone                 | BASE TABLE | InnoDB |      10 |
| def           | mysql        | procs_priv                | BASE TABLE | InnoDB |      10 |
| def           | mysql        | slave_master_info         | BASE TABLE | InnoDB |      10 |
| def           | sys          | sys_config                | BASE TABLE | InnoDB |      10 |
| def           | business     | mytable                   | BASE TABLE | InnoDB |      10 |
| def           | business     | tblstudent                | BASE TABLE | InnoDB |      10 |
| def           | business     | student                   | BASE TABLE | InnoDB |      10 |
| def           | business     | autoincrementtable        | BASE TABLE | InnoDB |      10 |
| def           | education    | university                | BASE TABLE | InnoDB |      10 |
| def           | education    | student                   | BASE TABLE | InnoDB |      10 |
+---------------+--------------+---------------------------+------------+--------+---------+
94 rows in set (4.68 sec)

このように、システムスキーマ（mysql、sys）からユーザー定義のデータベース（business、educationなど）まで、InnoDBを使用する全94件のテーブルが一括で取得できました。

実行例2：テーブル名だけをシンプルに表示する

テーブル名の一覧だけが必要な場合は、最初に紹介した構文を実行します。

mysql> SELECT table_name FROM INFORMATION_SCHEMA.TABLES
       WHERE ENGINE = 'InnoDB';

実行結果：

+---------------------------+
| TABLE_NAME                |
+---------------------------+
| innodb_table_stats        |
| innodb_index_stats        |
| db                        |
| user                      |
| default_roles             |
| role_edges                |
| global_grants             |
| password_history          |
| func                      |
| plugin                    |
| servers                   |
| tables_priv               |
| columns_priv              |
| help_topic                |
| help_category             |
| time_zone_name            |
| time_zone                 |
| procs_priv                |
| component                 |
| slave_relay_log_info      |
| slave_master_info         |
| gtid_executed             |
| server_cost               |
| engine_cost               |
| proxies_priv              |
| sys_config                |
| mytable                   |
| primarytable              |
| tblstudent                |
| student                   |
| autoincrementtable        |
| university                |
| dateadddemo               |
| distinctdemo              |
| demo1                     |
| primarytabledemo          |
| foreigntabledemo          |
| trailingandleadingdemo    |
| duplicatefound            |
+---------------------------+
94 rows in set (0.03 sec)

SELECT * の場合は4.68秒かかりましたが、必要な列だけを指定すると0.03秒で完了し、パフォーマンス面でも有利です。

補足：InnoDB以外のエンジンを指定する場合

WHERE 句の条件を変更すれば、他のストレージエンジンのテーブルも同様に確認できます。

-- MyISAMのテーブルを一覧表示
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MyISAM';

-- CSVのテーブルを一覧表示
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'CSV';

また、大文字小文字は区別されないため、'innodb' のように小文字で指定しても同じ結果になります。

まとめ

• INFORMATION_SCHEMA.TABLES を参照し、WHERE ENGINE = 'エンジン名' でフィルタリングすることで、特定のストレージエンジンを使用する全テーブルを取得できる。
• SELECT * なら行数・作成日時・照合順序などの詳細情報も確認可能。
• table_name 列だけを選択すれば、軽量かつ高速にテーブル名の一覧を取得できる。

データベースの保守やマイグレーション作業の際に、どのテーブルがどのエンジンを使用しているか把握しておくことは非常に重要です。ぜひこのクエリを活用してみてください。