MySQL - SHOW CREATE DATABASE Statement

---

---

MySQL SHOW CREATE DATABASE Statement

This query shows/displays the statement used to create the specified database. This displays the create statements along with the clauses.

Syntax

Following is the syntax of the SHOW CREATE DATABASE statement −

SHOW CREATE DATABASE [IF NOT EXISTS] database_name

Where, database_name is the name of the database.

Example

Suppose we have created a database as shown below −

CREATE DATABASE myDatabase;

The following query displays the query used to create the database −

Show CREATE DATABASE myDatabase;

Output

After executing the above query, it will generate the following output −

Database	Create Database
sampleDatabase	CREATE DATABASE `sampleDatabase` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

After alter statement

Once you alter the characteristics of a database using the ALTER statement the SHOW CREATE DATABASE Statement displays the query along with the characteristics.

Example

Suppose we have altered the above created database as shown below −

ALTER DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci 
ENCRYPTION = 'Y' READ ONLY = 1;

You can verify the characteristics of the created database as shown below −

SHOW CREATE DATABASE mydatabase;

Output

Following is the output of the above query −

Database	Create Database
mydatabase	CREATE DATABASE `mydatabase` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='Y' */ /* READ ONLY = 1 */

The IF NOT EXISTS clause

If you try to create a database with an existing name an error will be generated −

CREATE DATABASE myDatabase;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 10
Current database: sampledb
ERROR 1007 (HY000): Can't create database 'mydatabase'; database exists

If you use the IF NOT EXISTS clause along with the CREATE statement as shown below a new database will be created and if a database with the given name, already exists the query will be ignored.

CREATE DATABASE IF NOT EXISTS Test

If the CREATE DATABASE statement has an IF NOT EXISTS clause in it, then the result of the SHOW CREATE DATABASE Statement of the respective database also contains the IF NOT EXISTS clause.

SHOW CREATE DATABASE Test;

Output

The above query produces the following output −

Database	Create Database
Test	CREATE DATABASE `Test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

Example

You can use all the options in one query as follows −

ALTER DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci 
ENCRYPTION = 'Y' READ ONLY = 1;

You can verify the characteristics of the created database as shown below −

SHOW CREATE DATABASE mydatabase;

Output

Following is the output of the above mysql query −

Database	Create Database
mydatabase	CREATE DATABASE `mydatabase` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='Y' */ /* READ ONLY = 1 */