MySQLのすべてのデータベースから空でないテーブルの一覧を取得する方法

MySQLサーバー上にあるすべてのデータベースから、空でない（1行以上のデータを持つ）テーブルだけを抽出したい場合は、「information_schema.tables」を利用するのが便利です。このスキーマにはサーバー上の全テーブルに関するメタ情報が格納されており、table_rowsカラムを条件に指定することで、レコードを持つテーブルを簡単に絞り込めます。

基本のクエリ

以下のクエリでは、table_rowsが1以上のテーブル、つまり空でないテーブルのみを取得しています。table_typeとtable_nameを表示することで、テーブルの種類と名前を一目で確認できます。

mysql> select table_type, table_name from information_schema.tables
    -> where table_rows >= 1;

上記のクエリでは、行数が1以上のテーブル、すなわち空でないテーブルだけを対象としています。

実行結果

クエリを実行すると、次のような出力が得られます（紙面の都合上、一部を抜粋して掲載しています）。システムスキーマ（performance_schema や mysql など）に属するテーブルに加え、ユーザーが作成したテーブルも一緒にリストアップされることが分かります。

+------------+------------------------------------------------------+
| TABLE_TYPE | TABLE_NAME                                           |
+------------+------------------------------------------------------+
| BASE TABLE | innodb_table_stats                                   |
| BASE TABLE | innodb_index_stats                                   |
| BASE TABLE | cond_instances                                       |
| BASE TABLE | events_waits_current                                 |
| BASE TABLE | events_waits_history                                 |
| BASE TABLE | file_instances                                       |
| BASE TABLE | host_cache                                           |
| BASE TABLE | mutex_instances                                      |
| BASE TABLE | performance_timers                                   |
| BASE TABLE | setup_actors                                         |
| BASE TABLE | setup_consumers                                      |
| BASE TABLE | threads                                              |
| BASE TABLE | users                                                |
| BASE TABLE | accounts                                             |
| BASE TABLE | hosts                                                |
| BASE TABLE | session_connect_attrs                                |
| BASE TABLE | metadata_locks                                       |
| BASE TABLE | data_locks                                           |
| BASE TABLE | replication_connection_configuration                 |
| BASE TABLE | status_by_thread                                     |
| BASE TABLE | global_status                                        |
| BASE TABLE | session_status                                       |
| BASE TABLE | global_variables                                     |
| BASE TABLE | user_defined_functions                               |
| BASE TABLE | db                                                   |
| BASE TABLE | user                                                 |
| BASE TABLE | global_grants                                        |
| BASE TABLE | tables_priv                                          |
| BASE TABLE | help_topic                                           |
| BASE TABLE | general_log                                          |
| BASE TABLE | slow_log                                             |
| BASE TABLE | sys_config                                           |
| BASE TABLE | primarytable                                         |
| BASE TABLE | tblstudent                                           |
| BASE TABLE | student                                              |
| BASE TABLE | moviecollection                                      |
| BASE TABLE | autoincrementtable                                   |
| BASE TABLE | university                                           |
| BASE TABLE | sequencedemo                                         |
| BASE TABLE | rownumberdemo                                        |
| BASE TABLE | imagedemo                                            |
| BASE TABLE | usernameandpassworddemo                              |
| BASE TABLE | uniquedemo                                           |
| BASE TABLE | table1                                               |
| BASE TABLE | table2                                               |
| BASE TABLE | enumdemo                                             |
| BASE TABLE | bitdemo                                              |
| BASE TABLE | tinyintdemo                                          |
| BASE TABLE | countrycitydemo                                      |
| BASE TABLE | employeeinformation                                  |
| BASE TABLE | currenttimezone                                      |
| BASE TABLE | wheredemo                                            |
| BASE TABLE | mytabledemo                                          |
| BASE TABLE | lastinsertiddemo                                     |
| BASE TABLE | datetimedemo                                         |
| BASE TABLE | rankdemo                                             |
| BASE TABLE | groupbydemo                                          |
| BASE TABLE | product                                              |
| BASE TABLE | insertdemo                                           |
| BASE TABLE | updatedemo                                           |
| BASE TABLE | orderbydemo                                          |
| BASE TABLE | floordemo                                            |
| BASE TABLE | selectintodemo                                       |
+------------+------------------------------------------------------+

ポイント解説

• table_rows：各テーブルのおおよそのレコード数を示します。InnoDBテーブルの場合、この値は統計情報に基づく概算値となるため、正確な行数とは異なることがあります。
• table_type：「BASE TABLE」は通常のテーブルを、「VIEW」はビューを意味します。ビューを除外したい場合は、WHERE句にtable_type = 'BASE TABLE'を追加しましょう。
• 特定のデータベースに絞り込む：WHERE句にAND table_schema = 'データベース名'を追加すれば、対象を特定のデータベースに限定できます。

注意点

InnoDBストレージエンジンでは、table_rowsの値はキャッシュされた統計情報をもとにした近似値として返されます。厳密なレコード数を確認したい場合は、対象テーブルに対してCOUNT(*)を実行するのが確実です。また、パフォーマンススキーマなどシステム系テーブルが大量に含まれるため、必要に応じてtable_schemaでの絞り込みを組み合わせると、より見やすい結果になります。