SHOW CREATE TABLE
Syntax
SHOW CREATE TABLE tbl_name
Description
Shows the CREATE TABLE
statement that created the given
table. The statement requires the SELECT privilege
for the table. This statement also works with views and SEQUENCE
.
SHOW CREATE TABLE
quotes table and
column names according to the value of the sql_quote_show_create
server system variable.
MariaDB and MySQL-specific table options, column options, and index options are not included in the output of this statement if the NO_TABLE_OPTIONS
, NO_FIELD_OPTIONS
and NO_KEY_OPTIONS
SQL_MODE
flags are used.
MariaDB starting with 10.0.13
Invalid table options, column options and index options are normally commented out (note, that it is possible to create a table with invalid options, by altering a table of a different engine, where these options were valid). To have them uncommented, enable IGNORE_BAD_TABLE_OPTIONS
SQL_MODE
. Remember that replaying a CREATE TABLE
statement with uncommented invalid options will fail with an error, unless IGNORE_BAD_TABLE_OPTIONS
SQL_MODE
is in effect.
Note that SHOW CREATE TABLE
is not meant to provide metadata about a table. It provides information about how the table was declared, but the real table structure could differ a bit. For example, if an index has been declared as HASH
, the CREATE TABLE
statement returned by SHOW CREATE TABLE
will declare that index as HASH
; however, it is possible that the index is in fact a BTREE
, because the storage engine does not support HASH
.
Examples
SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `s` char(60) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
With sql_quote_show_create
off:
SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE t ( id int(11) NOT NULL AUTO_INCREMENT, s char(60) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1