【MySQL】特定のデータベース内のテーブル詳細情報を一覧表示するストアドプロシージャの作成方法

ここでは、queryという名前のデータベースを使用しており、その中に以下の2つのテーブルが存在する状況を想定して解説します。

mysql> SHOW TABLES IN query;
+-----------------+
| Tables_in_query |
+-----------------+
| student_detail  |
| student_info    |
+-----------------+
2 rows in set (0.00 sec)

ストアドプロシージャの作成

次に、指定したデータベース内のすべてのテーブルとその詳細情報を一覧表示するためのストアドプロシージャを作成します。このプロシージャでは、MySQLのシステムビューである Information_schema.tables を参照しています。

mysql> DELIMITER //
mysql> CREATE PROCEDURE tablelist()
    -> BEGIN
    -> SELECT * FROM Information_schema.tables WHERE table_schema = 'query';
    -> END //

Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER ;

コードのポイント

• DELIMITER：ストアドプロシージャ内のセミコロン（;）がステートメントの終端として誤って解釈されるのを防ぐため、区切り文字を一時的に「//」へ変更しています。プロシージャ作成後は元の区切り文字に戻します。
• Information_schema.tables：MySQLサーバー上のすべてのテーブルに関するメタデータ（テーブル名、ストレージエンジン、推定行数、照合順序、作成日時など）を保持しているシステムビューです。
• WHERE table_schema = 'query'：table_schema 列に対象のデータベース名を指定することで、該当データベース内のテーブルのみを抽出できます。この条件を変更すれば、任意のデータベースに対応可能です。

プロシージャの実行と出力結果

作成したプロシージャは、CALL文で実行します。列数が多いため、\Gを付けて縦方向に表示すると結果が読みやすくなります。

mysql> CALL tablelist()\G
*************************** 1. row ***************************
    TABLE_CATALOG: def
     TABLE_SCHEMA: query
       TABLE_NAME: student_detail
       TABLE_TYPE: BASE TABLE
          ENGINE: InnoDB
         VERSION: 10
      ROW_FORMAT: Dynamic
      TABLE_ROWS: 4
  AVG_ROW_LENGTH: 4096
     DATA_LENGTH: 16384
 MAX_DATA_LENGTH: 0
    INDEX_LENGTH: 0
       DATA_FREE: 0
   AUTO_INCREMENT: NULL
     CREATE_TIME: 2017-12-13 16:25:44
     UPDATE_TIME: NULL
      CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
        CHECKSUM: NULL
  CREATE_OPTIONS:
   TABLE_COMMENT:

*************************** 2. row ***************************
    TABLE_CATALOG: def
     TABLE_SCHEMA: query
       TABLE_NAME: student_info
       TABLE_TYPE: BASE TABLE
          ENGINE: InnoDB
         VERSION: 10
      ROW_FORMAT: Dynamic
      TABLE_ROWS: 4
  AVG_ROW_LENGTH: 4096
     DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
    INDEX_LENGTH: 0
       DATA_FREE: 0
   AUTO_INCREMENT: NULL
     CREATE_TIME: 2017-12-12 09:52:51
     UPDATE_TIME: NULL
      CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
        CHECKSUM: NULL
  CREATE_OPTIONS:
   TABLE_COMMENT:
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.06 sec)

まとめ

このように、Information_schema.tables を利用したストアドプロシージャを作成しておけば、SHOW TABLES コマンドでは得られない詳細なテーブル情報（使用しているストレージエンジン、推定行数、平均行長、データ長、作成日時、照合順序など）を一度に確認できます。データベース名を引数化すれば、より汎用的な管理ツールとして活用することも可能です。