MySQL in a Nutshell, 2nd Edition

Name

SHOW CREATE TABLE

Synopsis

SHOW CREATE TABLE table

This statement displays an SQL statement that can be used to create a table like the one named. The results may be copied and used with another database. You can also copy the results and modify the name of the table in order to use the CREATE statement on the same database. If you want a table exactly like an existing one, you might do better to use CREATE TABLE...LIKE... instead:

SHOW CREATE TABLE programmers \G

*************************** 1. row ***************************
Table: programmers
Create Table: CREATE TABLE 'programmers' (
              'prog_id' varchar(4) NOT NULL default '',
              'prog_name' varchar(50) NOT NULL default '',
              PRIMARY KEY ('prog_id')
              ) ENGINE=MyISAM DEFAULT CHARSET=latin1

Notice that the results include the table type and other default options.

As with the SHOW CREATE DATABASE statement, if you don’t want the table name in the results to be quoted with backticks as shown here, you can set the server variable SQL_QUOTE_SHOW_CREATE to 0 instead of its default value of 1.