If you’re using MySQL’s SHOW CREATE TABLE statement, but you don’t want quotes to be included in the output, this is easily addressed.

By default, SHOW CREATE TABLE (and SHOW CREATE DATABASE) includes quotes around table and column names, etc. When I say “by default”, I mean assuming you haven’t yet changed anything to disable these quotes.

Fortunately there’s an easy way to disable these quotes. MySQL provides us with the sql_quote_show_create system variable. This variable can be set to 1 or 0, which enables and disables such quotes respectively.

Example – With Quotes

First, let’s run the SHOW CREATE TABLE statement using the default option of including quotes:

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

We can see quotes around the table name, as well as the column names and also the index name.

Example – Remove Quotes

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

SET sql_quote_show_create = 0;
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 the same SQL statement was generated, but without quotes. That’s because I set the sql_quote_show_create system variable to 0.

How to Check for Quoted Identifiers

We can check the sql_quote_show_create system variable like this:

SELECT @@sql_quote_show_create;

Result:

+-------------------------+
| @@sql_quote_show_create |
+-------------------------+
|                       0 |
+-------------------------+

It’s currently set to 0, which means quotes are disabled when running SHOW CREATE TABLE and SHOW CREATE DATABASE. That’s because we disabled it in the previous example.

Restore to the Default Value

We can restore the sql_quote_show_create system variable to its default value by setting it to either 1 or DEFAULT:

SET sql_quote_show_create = DEFAULT;

Now let’s check it again:

SELECT @@sql_quote_show_create;

Result:

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

Here I specified DEFAULT, which set it to 1 (the default value). Using DEFAULT can be handy if you can’t remember what the default value is.