# SHOW CREATE TABLE

## Syntax

```sql
SHOW CREATE TABLE tbl_name
```

## Description

Shows the [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) statement that creates the given table. The statement requires the [SELECT privilege](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) for the table. This statement also works with [views](https://mariadb.com/docs/server/server-usage/views) and [SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/create-sequence).

`SHOW CREATE TABLE` quotes table and column names according to the value of the [sql\_quote\_show\_create](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#sql_quote_show_create) server system variable.

Certain [SQL\_MODE](https://mariadb.com/docs/server/server-management/variables-and-modes/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](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#no_table_options), [NO\_FIELD\_OPTIONS](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#no_field_options) and [NO\_KEY\_OPTIONS](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#no_key_options) [SQL\_MODE](https://mariadb.com/docs/server/server-management/variables-and-modes/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](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#ansi), [DB2](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#db2), [POSTGRESQL](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#postgresql), [MSSQL](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#mssql), [MAXDB](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#maxdb) or [ORACLE](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#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](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#ignore_bad_table_options) [SQL\_MODE](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode). Remember that replaying a [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) statement with uncommented invalid options will fail with an error, unless the [IGNORE\_BAD\_TABLE\_OPTIONS](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#ignore_bad_table_options) [SQL\_MODE](https://mariadb.com/docs/server/server-management/variables-and-modes/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 permits [TEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/text) and [BLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob) data types to be assigned a [DEFAULT](https://mariadb.com/docs/server/server-usage/tables/create-table#default) value. As a result, `SHOW CREATE TABLE` will append a `DEFAULT NULL` to nullable TEXT or BLOB fields if no specific default is provided.

{% tabs %}
{% tab title="Current" %}
Numbers are quoted in the `DEFAULT` clause in `SHOW CREATE` statement.
{% endtab %}

{% tab title="< 10.2.2" %}
Numbers are not quoted in the `DEFAULT` clause in `SHOW CREATE` statement.
{% endtab %}
{% endtabs %}

### 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

```sql
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](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#sql_quote_show_create) off:

```sql
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
```

[SQL\_MODE](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode) impacting the output:

```sql
SELECT @@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")
```

## See Also

* [SHOW CREATE SEQUENCE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-sequence)
* [SHOW CREATE VIEW](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-view)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}