SHOW CREATE TABLE

This MySQL statement displays an SQL statement that can be used to create a MySQL table like the one named. The results may be copied and used with another MySQL database.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SHOW CREATE TABLE table

Explanation

This MySQL statement displays an SQL statement that can be used to create a MySQL table like the one named. The results may be copied and used with another MySQL 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.

Examples

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 MySQL 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 back-ticks as shown here, you can set the server variable SQL_QUOTE_SHOW_CREATE to 0, instead of its default value of 1.