Can I store the output of SHOW CREATE TABLE tablename into a variable?

Question

Can I store the output of SHOW CREATE TABLE tablename into a variable?

If possible, how would I do that?

Answer 1

This is not possible. Although the return value looks like a standard data set, the SQL parser won't allow it in such standard queries as this:

SELECT `Create Table` INTO @foo FROM (SHOW CREATE TABLE bar);
-- results in ERROR 1064 near 'SHOW CREATE TABLE bar'

You can query the information_schema database to get the information you want though. This will provide similar output:

SELECT CONCAT(
    'CREATE TABLE `',
    t.TABLE_NAME,
    '`(\n',
    GROUP_CONCAT(
        CONCAT(
            '  `',
            c.COLUMN_NAME,
            '` ',
            c.COLUMN_TYPE,
            ' ',
            IF(c.DATA_TYPE = 'varchar', CONCAT('COLLATE ', c.COLLATION_NAME, ' '), ''),
            IF(c.IS_NULLABLE = 'NO', 'NOT NULL', ''),
            IFNULL(CONCAT(' DEFAULT ', c.COLUMN_DEFAULT), ''),
            ' ',
            c.EXTRA
        )
        SEPARATOR ',\n'
    ),
    '\n) ENGINE=',
    t.ENGINE,
    ' AUTO_INCREMENT=',
    t.AUTO_INCREMENT,
    ' DEFAULT CHARSET=',
    s.DEFAULT_CHARACTER_SET_NAME,
    ' COLLATE=',
    t.TABLE_COLLATION
) AS create_statement
FROM information_schema.schemata s
INNER JOIN information_schema.tables t
    ON s.SCHEMA_NAME = t.TABLE_SCHEMA
INNER JOIN information_schema.columns c
    ON (c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME)
WHERE s.SCHEMA_NAME = 'mydatabase' AND t.TABLE_NAME = 'mytable';

The notable exception being key information.

Answer 2

You can do it with the results, with php for example:

function getCreateSQLFromTableName($table){
    $sql = "SHOW CREATE TABLE $table";
    echo $sql;
    $res = mysql_query($sql);
    if($res){
        $row = mysql_fetch_assoc($res);
        $createSQL = $row['Create Table'];
        return $createSQL;
    }
    else{
        echo mysql_errno() . ": " . mysql_error(). "\n";
    }


}