title: mysql stored procedure executing SHOW CREATE TABLE
body: <p>I'm trying to write a stored procedure that wraps around SHOW CREATE TABLE.  My final goal is to dynamically look up the schema name from information_schema and run SHOW CREATE TABLE schema.tableName.</p>
  
  <p>It seems that I can't run SHOW CREATE TABLE inside a store procedure.</p>
  
  <pre><code>DELIMITER $$
  DROP PROCEDURE IF EXISTS ct$$
  CREATE PROCEDURE ct (tableName VARCHAR(50))
  BEGIN
  
    SHOW CREATE TABLE tableName;
  
  END$$
  DELIMITER ;
  
  
  mysql&gt; CALL ct('users');
  ERROR 1146 (42S02): Table 'adcentraldb.tableName' doesn't exist
  mysql&gt; 
  </code></pre>
  
  <hr>
  
  <p>For those that are interested this is what I end up with for the SHOW CREATE TABLE wrapper that is working</p>
  
  <pre><code>DELIMITER $$
  DROP PROCEDURE IF EXISTS ct$$
  
  -- Wraps around SHOW CREATE TABLE.  Look at other schemas other than current.
  CREATE PROCEDURE ct (tableName VARCHAR(50))
  BEGIN
    DECLARE dbName VARCHAR(50);
  
    SET dbName = (SELECT `TABLE_SCHEMA` FROM `INFORMATION_SCHEMA`.`TABLES` 
    WHERE `TABLE_NAME` = tableName LIMIT 1);
  
    SET @a=CONCAT("SHOW CREATE TABLE ", dbName, '.', tableName);
    PREPARE stmt1 FROM @a;
    EXECUTE stmt1; 
    DEALLOCATE PREPARE stmt1;
  
  END$$
  DELIMITER ;
  </code></pre>