Get the SQL statement to recreate a table. This statement shows the complete CREATE TABLE syntax, including column definitions and indexes.
Shows the CREATE TABLE statement that creates the given table. The statement requires the SELECT privilege for the table. This statement also works with and .
SHOW CREATE TABLE quotes table and column names according to the value of the server system variable.
Certain values can result in parts of the original CREATE statement not being included in the output. MariaDB-specific table options, column options, and index options are not included in the output of this statement if the , and flags are used. All MariaDB-specific table attributes are also not shown when a non-MariaDB/MySQL emulation mode is used, which includes , , , , or .
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 the . Remember that replaying a statement with uncommented invalid options will fail with an error, unless the 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.
MariaDB permits and data types to be assigned a value. As a result, SHOW CREATE TABLE will append a DEFAULT NULL to nullable TEXT or BLOB fields if no specific default is provided.
Numbers are quoted in the DEFAULT clause in SHOW CREATE statement.
Numbers are not quoted in the DEFAULT clause in SHOW CREATE statement.
Indexes are sorted and displayed in the following order, which may differ from the order of the CREATE TABLE statement.
PRIMARY KEY
UNIQUE keys where all column are NOT NULL
UNIQUE keys that don't contain partial segments
Other UNIQUE keys
See sql/sql_table.cc for details.
With off:
impacting the output:
This page is licensed: GPLv2, originally from
SHOW CREATE TABLE tbl_nameNormal keys
Fulltext keys
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=latin1SHOW 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=latin1SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`msg` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`msg` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SET SQL_MODE=ORACLE;
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE "t1" (
"id" int(11) NOT NULL,
"msg" varchar(100) DEFAULT NULL,
PRIMARY KEY ("id")