MySQLストアドプロシージャでSHOW CREATE TABLEを実行する方法

MySQLストアドプロシージャでSHOW CREATE TABLEを実行する方法

ストアドプロシージャの中でSHOW CREATE TABLEを実行したい場合は、動的SQL（プリペアドステートメント）を利用します。SHOW系のコマンドはプロシージャ内に直接記述できないため、SQL文を文字列として組み立てて実行するのがポイントです。

まずはサンプル用のテーブルを作成しましょう。

mysql> create table DemoTable2011
    -> (
    -> StudentId int NOT NULL AUTO_INCREMENT,
    -> StudentName varchar(20),
    -> StudentAge int,
    -> StudentCountryName varchar(20),
    -> PRIMARY KEY(StudentId)
    -> );
Query OK, 0 rows affected (0.80 sec)

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

次に、引数で受け取ったテーブル名をもとにSHOW CREATE TABLEを実行するストアドプロシージャを定義します。

mysql> delimiter //
mysql> create procedure test_show_create_demo(table_name varchar(100))
    -> begin
    -> set @query=concat("SHOW CREATE TABLE ",table_name);
    -> prepare st from @query;
    -> execute st;
    -> end
    -> //
Query OK, 0 rows affected (0.22 sec)

mysql> delimiter ;

このプロシージャでは、CONCAT()関数でSQL文を文字列として組み立て、PREPAREとEXECUTEによって動的に実行しています。テーブル名を引数として渡せるため、どのテーブルにも対応できる汎用的な仕組みになっています。

ストアドプロシージャの呼び出し

CALLコマンドを使ってストアドプロシージャを呼び出します。

mysql> call test_show_create_demo('DemoTable2011');

実行すると、以下のように対象テーブルのCREATE TABLE文が出力されます。

+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DemoTable2011 | CREATE TABLE `demotable2011` (
   `StudentId` int(11) NOT NULL AUTO_INCREMENT,
   `StudentName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
   `StudentAge` int(11) DEFAULT NULL,
   `StudentCountryName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`StudentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.06 sec)

ポイントまとめ

• SHOW系のコマンドはストアドプロシージャ内に直接書けないため、動的SQLを使う必要があります。
• CONCAT()でSQL文を組み立て、PREPARE〜EXECUTEの流れで実行します。
• テーブル名を引数化することで、任意のテーブル定義を取得できる再利用性の高いプロシージャになります。