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.
 
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.
 
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. 
 
From MariaDB 10.2.2, numbers are no longer quoted in the
DEFAULT clause in SHOW CREATE statement. Previously, MariaDB
quoted numbers. 
 
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
 


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