You are viewing an older version of this section. View current production version.
Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

SHOW CREATE TABLE

Shows the CREATE TABLE statement that was used to create the table.

Syntax

SHOW CREATE TABLE table_name

Arguments

table_name

The name of the table.

Remarks

  • In the output, MemSQL specific table attributes are enclosed in the blocks /*! and */. These blocks are not comments, as the entire output of SHOW CREATE TABLE can be used to recreate the table.
  • This command can be run on any MemSQL node (see Node Requirements for MemSQL Commands).

Examples

Example 1

The following example shows the output of SHOW CREATE TABLE for a table with sparse compression.

Create the table:

CREATE TABLE table_1(a INT, b DECIMAL) COMPRESSION = SPARSE;

Show the table:

SHOW CREATE TABLE table_1;

Output of the “Create Table” column for the table_1 row:

CREATE TABLE `table_1` (
  `a` int(11) DEFAULT NULL,
  `b` decimal(10,0) DEFAULT NULL
  /*!90618 , SHARD KEY () */ 
) /*!90623 AUTOSTATS_CARDINALITY_MODE=PERIODIC, AUTOSTATS_HISTOGRAM_MODE=CREATE */ /*!90623 SQL_MODE='STRICT_ALL_TABLES' */
  /*!90623 COMPRESSION=SPARSE */

Example 2

The following example shows the output of SHOW CREATE TABLE for a columnstore table.

Create the table:

CREATE TABLE table_2(a INT, b VARCHAR(50), KEY(b) USING CLUSTERED COLUMNSTORE);

Show the table:

SHOW CREATE TABLE table_2;

Output of the “Create Table” for the table_2 row:

CREATE TABLE `table_2` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  KEY `b` (`b`) /*!90619 USING CLUSTERED COLUMNSTORE */
  /*!90618 , SHARD KEY () */ 
) /*!90623 AUTOSTATS_CARDINALITY_MODE=INCREMENTAL, AUTOSTATS_HISTOGRAM_MODE=CREATE, AUTOSTATS_SAMPLING=ON */ /*!90623 SQL_MODE='STRICT_ALL_TABLES' */