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.

Certain SQL_MODE 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 NO_TABLE_OPTIONS, NO_FIELD_OPTIONS and NO_KEY_OPTIONS SQL_MODE flags are
used. All MariaDB-specific table attributes are also not shown when a
non-MariaDB/MySQL emulation mode is used, which includes ANSI, DB2,
POSTGRESQL, MSSQL, MAXDB or ORACLE.

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 IGNORE_BAD_TABLE_OPTIONS SQL_MODE. Remember
that replaying a CREATE TABLE statement with uncommented invalid options will
fail with an error, unless the 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.

MariaDB starting with 10.2.1
----------------------------
MariaDB 10.2.1 permits TEXT and BLOB data types to be assigned a DEFAULT
value. As a result, from MariaDB 10.2.1, SHOW CREATE TABLE will append a
DEFAULT NULL to nullable TEXT or BLOB fields if no specific default is
provided.

MariaDB starting with 10.2.2
----------------------------
From MariaDB 10.2.2, numbers are no longer quoted in the DEFAULT clause in
SHOW CREATE statement. Previously, MariaDB quoted numbers.

Index Order
-----------

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
* LONG UNIQUE keys
* Normal keys
* Fulltext keys

See sql/sql_table.cc for details.

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

Unquoted numeric DEFAULTs, from MariaDB 10.2.2:

CREATE TABLE td (link TINYINT DEFAULT 1);

SHOW CREATE TABLE td\G
*************************** 1. row ***************************
   Table: td
Create Table: CREATE TABLE `td` (
 `link` tinyint(4) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Quoted numeric DEFAULTs, until MariaDB 10.2.1:

CREATE TABLE td (link TINYINT DEFAULT 1);

SHOW CREATE TABLE td\G
*************************** 1. row ***************************
   Table: td
Create Table: CREATE TABLE `td` (
 `link` tinyint(4) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SQL_MODE impacting the output:

SELECT @@sql_mode;
+------------------------------------------------------------------------------
------------+
| @@sql_mode                                                                  
      |
+------------------------------------------------------------------------------
------------+
|
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SU
STITUTION |
+------------------------------------------------------------------------------
------------+

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


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")

URL: https://mariadb.com/kb/en/show-create-table/