MySQLストアドプロシージャでデータベース名をパラメータ化し、テーブルの詳細情報を一覧表示する方法

はじめに

MySQLでは、システムビュー information_schema.tables を参照することで、特定のデータベースに含まれるテーブルの詳細なメタデータ（ストレージエンジン、概算行数、作成日時、照合順序など）を一度に取得できます。本記事では、データベース名をパラメータとして受け取るストアドプロシージャを作成し、そのデータベース内のテーブル一覧を詳細情報付きで表示する方法を解説します。

前提条件：サンプルデータベース

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

mysql> Show tables in query;
+-----------------+
| Tables_in_query |
+-----------------+
| student_detail |
| student_info |
+-----------------+
2 rows in set (0.00 sec)

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

次のストアドプロシージャは、データベース名を引数として受け取り、そのデータベースに属する全テーブルの詳細情報を一覧形式で返します。

mysql> DELIMITER//
mysql> CREATE procedure tb_list(db_name varchar(40))
 -> BEGIN
 -> SET @z := CONCAT('Select * from information_schema.tables WHERE table_schema = ','\'',db_name,'\'');
 -> Prepare stmt from @z;
 -> EXECUTE stmt;
 -> END //
Query OK, 0 rows affected (0.06 sec)

コードのポイント

• DELIMITER // … プロシージャ本体内のセミコロン（;）が即座に文の終端として解釈されるのを防ぐため、区切り文字を一時的に変更しています。定義完了後は DELIMITER; で元に戻します。
• CONCAT() … 引数で受け取ったデータベース名をシングルクォートで囲んだ上で、information_schema.tables を検索するSELECT文を動的に組み立てます。
• PREPARE / EXECUTE … 文字列として生成したSQLをプリペアドステートメントとして準備・実行することで、ストアドプロシージャ内から動的クエリを扱えるようにしています。

ストアドプロシージャの実行

作成したプロシージャは、CALL文でデータベース名を引数に渡して実行します。

mysql> DELIMITER;
mysql> CALL tb_list('query')\G

実行すると、以下のようにテーブルごとの詳細情報が縦型形式（\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.00 sec)

主な出力項目の意味

• TABLE_SCHEMA … テーブルが属するデータベース（スキーマ）名
• TABLE_NAME … テーブル名
• TABLE_TYPE … BASE TABLE（実テーブル）か VIEW（ビュー）かの区別
• ENGINE … 採用されているストレージエンジン（例：InnoDB）
• TABLE_ROWS … 概算の行数（InnoDBの場合は統計情報に基づく推定値）
• DATA_LENGTH / INDEX_LENGTH … データ部およびインデックス部のサイズ（バイト単位）
• CREATE_TIME … テーブルの作成日時
• TABLE_COLLATION … テーブルの既定の照合順序

まとめ

ストアドプロシージャと動的SQL（PREPARE／EXECUTE）を組み合わせれば、データベース名を引数として柔軟にテーブル情報を取得するユーティリティを作成できます。SHOW TABLES FROM データベース名; でも一覧は取得できますが、information_schema.tables を直接参照するこの手法では、行数・エンジン種別・作成日時といった詳細なメタデータまで同時に確認できる点が大きな利点です。運用監視やスキーマ調査のスクリプトにも応用できるため、ぜひ活用してみてください。