CODEWITHSUNDEEP
mysqlsql
user2782001
user2782001

Reputation: 3498

mysql use SHOW CREATE TABLE with dynamic table name

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`=?)

Upvotes: 1

Views: 676

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

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.

Upvotes: 1

Related Questions