In MySQL, we can use the SHOW CREATE TABLE statement to produce a CREATE TABLE statement from an existing table.

This enables us to generate a script that we can use to recreate the table on another database, or on the same database at a later date. Or we could use it to create another similar table. We would need to modify the code accordingly when doing this, but it could give us a good starting point.

We can also use SHOW CREATE TABLE for a quick way to take a look at the table’s structure, and check for things like data types, primary keys, foreign keys, etc.

Syntax

The syntax goes like this:

SHOW CREATE TABLE tbl_name

Where tbl_name is the name of the table that we want to generate the CREATE TABLE script for.

Example

Here’s an example to demonstrate:

SHOW CREATE TABLE actor;

Result:

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| actor | CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Here I ran SHOW CREATE TABLE against the actor table in the sakila sample database.

We can take the above CREATE TABLE statement and run it against any other database to create an identical table.

We could also use this script as a basis for another table – changing the name and other properties as required.

The table and column names are quoted according to the value of the sql_quote_show_create option. This option allows us to enable or disable the quoting of identifiers for the SHOW CREATE TABLE and SHOW CREATE DATABASE statements.

Checking for Quoted Identifiers

We can check our sql_quote_show_create option like this:

SELECT @@sql_quote_show_create;

Result:

+-------------------------+
| @@sql_quote_show_create |
+-------------------------+
|                       1 |
+-------------------------+

In my case sql_quote_show_create is enabled.

Removing the Quotes

We can disable the quotes by setting the sql_quote_show_create to 0:

SET sql_quote_show_create = 0;

Now let’s run the SHOW CREATE TABLE statement again:

SHOW CREATE TABLE actor;

Result:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| actor | CREATE TABLE actor (
  actor_id smallint unsigned NOT NULL AUTO_INCREMENT,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (actor_id),
  KEY idx_actor_last_name (last_name)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This time there are no quotes around the table and column names.

For more information about SHOW CREATE TABLE, see MySQL’s documentation for SHOW CREATE TABLE.