mysql use SHOW CREATE TABLE with dynamic table name

Question

I want to be able to see the sql text from SHOW CREATE TABLE for any table for debugging purposes. Can I do that with prepared escaping?

// doesn't work because table name is escaped as string
 SHOW CREATE TABLE ?

//doesn't work, syntax is not allowed    
SHOW CREATE TABLE (SELECT table_name FROM `INFORMATION_SCHEMA.tables`   WHERE  `TABLE_NAME`=?)

Just use the select from information_schema without SHOW CREATE TABLE — juergen d
– juergen d, Commented Apr 7, 2016 at 19:57
"Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth." -- dev.mysql.com/doc/refman/5.7/en/prepare.html — axiac
– axiac, Commented Apr 7, 2016 at 20:12

Lukasz Szozda · Accepted Answer · 2016-04-07 20:12:30Z

You could use prepared statement:

-- here goes your table name, could be parametrized to call from app
SET @tab_name = '`tab`';   

SET @query = REPLACE('SHOW CREATE TABLE @tab_name', '@tab_name', @tab_name);

-- SELECT @query;

PREPARE stmt1 FROM @query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SqlFiddleDemo

Output:

╔════════╦═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ Table  ║                                                    Create Table                                                     ║
╠════════╬═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ tab    ║ CREATE TABLE `tab` ( `col1` int(11) DEFAULT NULL, `col2` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ║
╚════════╩═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

If you need it for every table you could wrap it with loop or cursor.

Warning:

You should quote @tab_name in application layer to avoid SQL Injection.

Collectives™ on Stack Overflow

mysql use SHOW CREATE TABLE with dynamic table name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related