Stored procedure MySQL thực thi SHOW CREATE TABLE

Dùng prepared statement trong procedure để chạy SHOW CREATE TABLE động. Tạo bảng:

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)

Tạo procedure:

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 ;

Gọi procedure:

mysql> call test_show_create_demo('DemoTable2011');

Kết quả hiển thị câu lệnh CREATE TABLE đầy đủ:

+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)