Syntax:
SHOW CREATE TABLE tbl_name

Shows the CREATE TABLE statement that creates the named table. To use
this statement, you must have some privilege for the table. This
statement also works with views.

As of MySQL 8.0.16, MySQL implements CHECK constraints and SHOW CREATE
TABLE displays them. All CHECK constraints are displayed as table
constraints. That is, a CHECK constraint originally specified as part
of a column definition displays as a separate clause not part of the
column definition. Example:

mysql> CREATE TABLE t1 (
         i1 INT CHECK (i1 <> 0),      -- column constraint
         i2 INT,
         CHECK (i2 > i1),             -- table constraint
         CHECK (i2 <> 0) NOT ENFORCED -- table constraint, not enforced
       );

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i1` int DEFAULT NULL,
  `i2` int DEFAULT NULL,
  CONSTRAINT `t1_chk_1` CHECK ((`i1` <> 0)),
  CONSTRAINT `t1_chk_2` CHECK ((`i2` > `i1`)),
  CONSTRAINT `t1_chk_3` CHECK ((`i2` <> 0)) /*!80016 NOT ENFORCED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SHOW CREATE TABLE quotes table and column names according to the value
of the sql_quote_show_create option. See
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html.

When altering the storage engine of a table, table options that are not
applicable to the new storage engine are retained in the table
definition to enable reverting the table with its previously defined
options to the original storage engine, if necessary. For example, when
changing the storage engine from InnoDB to MyISAM, options specific to
InnoDB, such as ROW_FORMAT=COMPACT, are retained, as shown here:

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPACT ENGINE=InnoDB;
mysql> ALTER TABLE t1 ENGINE=MyISAM;
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int NOT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT

When creating a table with strict mode disabled, the storage engine's
default row format is used if the specified row format is not
supported. The actual row format of the table is reported in the
Row_format column in response to SHOW TABLE STATUS. SHOW CREATE TABLE
shows the row format that was specified in the CREATE TABLE statement.

In MySQL 8.0.30 and later, SHOW CREATE TABLE includes the definition of
the table's generated invisible primary key, if it has such a key, by
default. You can cause this information to be suppressed in the
statement's output by setting
show_gipk_in_create_table_and_information_schema = OFF. For more
information, see
https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html

