MySQLですべてのデータベースと各データベースの全テーブルを一覧表示する方法

INFORMATION_SCHEMAを使って一括取得する方法

MySQLでは通常、SHOW DATABASES;やSHOW TABLES;コマンドを使って個別に確認できますが、「サーバー上のすべてのデータベース」と「それぞれのデータベースに属するすべてのテーブル」を一度に取得したい場合は、INFORMATION_SCHEMAを利用するのが効果的です。

基本となる構文は以下の通りです。

SELECT my_schema.SCHEMA_NAME, GROUP_CONCAT(tbl.TABLE_NAME)
FROM information_schema.SCHEMATA my_schema
LEFT JOIN information_schema.TABLES tbl
  ON my_schema.SCHEMA_NAME = tbl.TABLE_SCHEMA
GROUP BY my_schema.SCHEMA_NAME;

クエリのポイント

• information_schema.SCHEMATA: サーバー上に存在するすべてのデータベース（スキーマ）の一覧を保持するテーブルです。
• information_schema.TABLES: 各データベースに含まれるテーブルの情報を保持しています。
• GROUP_CONCAT(): グループごとにテーブル名をカンマ区切りの文字列として連結します。
• LEFT JOIN: 内部結合ではなくLEFT JOINを使うことで、テーブルが1つも存在しない空のデータベースも結果に表示されます（この場合NULLが返ります）。

実行例

実際に上記のクエリを実行してみましょう。

mysql> SELECT my_schema.SCHEMA_NAME, GROUP_CONCAT(tbl.TABLE_NAME)
    -> FROM information_schema.SCHEMATA my_schema
    -> LEFT JOIN information_schema.TABLES tbl
    ->   ON my_schema.SCHEMA_NAME = tbl.TABLE_SCHEMA
    -> GROUP BY my_schema.SCHEMA_NAME;

実行すると、データベース名とそれに対応するテーブル一覧が出力されます。実際の出力は非常に長くなるため、ここでは一部を抜粋して紹介します。

+----------------------+------------------------------------------------------------------+
| SCHEMA_NAME          | GROUP_CONCAT(tbl.TABLE_NAME)                                     |
+----------------------+------------------------------------------------------------------+
| bothinnodbandmyisam  | employee,gradedemo,student,student_information                   |
| business             | addconstraintdemo,addonedaydemo,autoincrementtozero,college,...  |
| commandline          | caseinsensitivedistinctdemo,insertmaxplus1demo,instructor,...    |
| customer-tracker     | NULL                                                             |
| customer_tracker_database | NULL                                                        |
| demo                 | mytable                                                          |
| education            | student,university                                               |
| hb_student_tracker   | demotable194,demotable202,demotable210,student,...               |
| hello                | NULL                                                             |
| information_schema   | COLUMNS,INNODB_CMP,VIEWS,ENGINES,TABLES,SCHEMATA,TRIGGERS,...    |
| mysql                | db,help_category,user,tables_priv,time_zone,func,...             |
| performance_schema   | accounts,cond_instances,events_statements_current,threads,...    |
| rdb                  | boy,girl                                                         |
| sample               | accumulateddemo,autoincrementdemo,columndoesnotexists,...        |
| sys                  | host_summary_by_file_io,schema_object_overview,user_summary,...  |
| test                 | customers,studentinfo,dateformatdemo,decimaldemo,...             |
| test3                | groupwithtopndemo,productdemo,studentinformation,posts,...       |
| tracker              | preventnegativenumbers                                           |
| web                  | demotable492,DemoTable,demotable605,select,demotable484,...      |
| web_tracker          | NULL                                                             |
+----------------------+------------------------------------------------------------------+
36 rows in set, 6 warnings (0.18 sec)

結果の読み方

この出力からは、以下のことが確認できます。

• テーブルを持つデータベースには、そのすべてのテーブル名がカンマ区切りで表示されます。
• 「customer-tracker」や「hello」など、テーブルを1つも持たないデータベースには「NULL」が表示されます。
• information_schema、mysql、performance_schema、sysといったシステムデータベースも一覧に含まれます。これらを除外したい場合は、WHERE句でmy_schema.SCHEMA_NAME NOT IN ('information_schema','mysql','performance_schema','sys')のように条件を追加するとよいでしょう。

補足： テーブル数が多い場合の注意点

システム変数group_concat_max_lenのデフォルト値（1024バイト）を超えると、テーブル一覧が途中で切り捨てられます。多数のテーブルを持つデータベースを完全に表示したい場合は、事前に以下のように上限値を引き上げておきましょう。

SET SESSION group_concat_max_len = 1000000;

まとめ

INFORMATION_SCHEMAのSCHEMATAとTABLESを結合し、GROUP_CONCATを組み合わせることで、すべてのデータベースと各データベースの全テーブルをたった1つのクエリで効率よく確認できます。空のデータベースも漏れなく表示したい場合は、LEFT JOINを使用するのがポイントです。