# 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" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-table.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.