How To See the CREATE TABLE Statement of an Existing Table? - MySQL FAQs - Understanding SQL CREATE, ALTER and DROP Statements

If you want to know how an existing table was created, you can use the "SHOW CREATE TABLE" command to get a copy of the "CREATE TABLE" statement back on an existing table. The following tutorial script shows you a good example:

mysql> SHOW CREATE TABLE tip;

+-------+-------------------------------
| Table | Create Table
+-------+-------------------------------
| tip   | CREATE TABLE `tip` (
  `id` int(11) NOT NULL,
  `subject` varchar(80) NOT NULL,
  `description` varchar(256) NOT NULL,
  `create_date` date default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------
1 row in set (0.38 sec)

Comparing with the original "CREATE TABLE" statement used in the previous tutorial, the output tells you that:

• INTEGER data type was replaced by "int(11)".
• Default database engine "MyISAM" was used for the table.
• Default character set "latin1" was used for the table.