【MySQL】テーブル一覧を取得する

SHOW TABLESでテーブル一覧を確認する

MySQLでテーブル一覧を確認するには、SHOW TABLES文を使う。

mysql> SHOW TABLES;
+------------------+
| Tables_in_shopdb |
+------------------+
| orders           |
| users            |
| users_view       |
+------------------+

結果の列名はTables_in_接続中のデータベース名になる。
テーブルだけでなくビューも表示される。

別のデータベースのテーブル一覧を確認する

FROM句にデータベース名を指定すると、接続を切り替えずに別のデータベースのテーブル一覧を確認できる。

mysql> SHOW TABLES FROM analytics;
+---------------------+
| Tables_in_analytics |
+---------------------+
| events              |
+---------------------+

テーブルとビューを区別する

FULLを付けると、Table_type列が追加され、テーブルとビューを区別できる。

mysql> SHOW FULL TABLES;
+------------------+------------+
| Tables_in_shopdb | Table_type |
+------------------+------------+
| orders           | BASE TABLE |
| users            | BASE TABLE |
| users_view       | VIEW       |
+------------------+------------+

Table_typeはテーブルの場合BASE TABLE、ビューの場合VIEWになる。

名前で絞り込む

LIKE句を付けると、名前が一致するテーブルだけを表示する。

mysql> SHOW TABLES LIKE 'user%';
+--------------------------+
| Tables_in_shopdb (user%) |
+--------------------------+
| users                    |
| users_view               |
+--------------------------+

WHERE句でも絞り込める。
Table_type列を条件にする場合はFULLを付ける必要がある。

mysql> SHOW FULL TABLES WHERE Table_type = 'VIEW';
+------------------+------------+
| Tables_in_shopdb | Table_type |
+------------------+------------+
| users_view       | VIEW       |
+------------------+------------+

information_schema.tablesでテーブル一覧を確認する

SQLでテーブル一覧を取得する場合はinformation_schema.tablesビューを参照する。

mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
    -> FROM information_schema.tables
    -> WHERE TABLE_SCHEMA = 'shopdb';
+---------------+--------------+------------+------------+--------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |
+---------------+--------------+------------+------------+--------+
| def           | shopdb       | orders     | BASE TABLE | InnoDB |
| def           | shopdb       | users      | BASE TABLE | InnoDB |
| def           | shopdb       | users_view | VIEW       | NULL   |
+---------------+--------------+------------+------------+--------+

TABLE_SCHEMAはMySQLでは「データベース」と同じ意味である。
TABLE_SCHEMAで絞らない場合、すべてのデータベースのテーブルが一覧に含まれる。
参考: 【MySQL】データベース一覧を確認する

ビューのENGINE列はNULLになる。

information_schema.tablesの主なカラム

information_schema.tablesには以下のようなカラムが存在する。

TABLE_CATALOG: カタログ名（MySQLでは常にdef）
TABLE_SCHEMA: データベース名
TABLE_NAME: テーブル名
TABLE_TYPE: テーブルの種類（BASE TABLE・VIEW・SYSTEM VIEW）
ENGINE: ストレージエンジン（InnoDB・MyISAMなど、ビューはNULL）
TABLE_ROWS: 概算の行数
CREATE_TIME: テーブルの作成日時
TABLE_COMMENT: テーブルコメント

TABLE_ROWSはInnoDBの場合正確な行数ではなく概算値である点に注意する。
正確な件数が必要な場合はCOUNT(*)を使う。

テーブルだけに絞る

ビューを除いてテーブルだけに絞る場合はTABLE_TYPEをBASE TABLEに指定する。

SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_SCHEMA = 'shopdb' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

+------------+
| TABLE_NAME |
+------------+
| orders     |
| users      |
+------------+

カラム一覧も合わせて取得する

各テーブルのカラム一覧も取得する場合は、information_schema.columnsテーブルをJOINする。

SELECT t.TABLE_NAME, t.TABLE_TYPE, c.COLUMN_NAME, c.IS_NULLABLE, c.DATA_TYPE
FROM information_schema.tables t
LEFT JOIN information_schema.columns c
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'shopdb' AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;

+------------+------------+-------------+-------------+-----------+
| TABLE_NAME | TABLE_TYPE | COLUMN_NAME | IS_NULLABLE | DATA_TYPE |
+------------+------------+-------------+-------------+-----------+
| orders     | BASE TABLE | id          | NO          | int       |
| orders     | BASE TABLE | user_id     | NO          | int       |
| orders     | BASE TABLE | amount      | NO          | int       |
| users      | BASE TABLE | id          | NO          | int       |
| users      | BASE TABLE | name        | NO          | varchar   |
| users      | BASE TABLE | email       | YES         | varchar   |
| users      | BASE TABLE | address     | YES         | varchar   |
+------------+------------+-------------+-------------+-----------+

JOINの条件にはTABLE_SCHEMAとTABLE_NAMEの両方を指定する。
TABLE_NAMEだけでJOINすると、他のデータベースに同名のテーブルがある場合に余計な行が結合されるため注意する。

権限のあるテーブルだけが一覧に表示される

information_schema.tablesは権限で絞り込まれるビューであり、SELECTなどの権限を持たないテーブルは一覧に表示されない。
SHOW TABLESも同様の絞り込みを受ける。

例えばusersテーブルにのみSELECT権限を持つapp_userで接続すると、ordersテーブルとusers_viewビューは表示されない。

mysql> SHOW TABLES;
+------------------+
| Tables_in_shopdb |
+------------------+
| users            |
+------------------+