SQL DROP DATABASE Statement

---

---

The SQL DROP DATABASE Statement

The DROP DATABASE statement in SQL is used to permanently delete an existing database and all of its contents. This operation removes the entire database, including all tables, views, stored procedures, and the data stored inside it.

You need to be very careful when using the DROP DATABASE command. It will permanently delete all data in the database and cannot be undone. Always make sure you have a backup before running this command.

Syntax

Following is the syntax of the DROP DATABASE statement in SQL:

DROP DATABASE database_name;

Here,

• DROP DATABASE: It is the SQL command to delete a database.
• database_name: It is the name of the database you want to delete.

Example

Following is the command to delete a database named testDB:

DROP DATABASE testDB;

After executing the above command in MySQL database, we get the following output:

Query OK, 0 rows affected (0.04 sec)

How to Verify Whether a Database Is Dropped

To confirm that a database has been dropped successfully, you can list all databases using the following SQL command:

SHOW DATABASES;

After executing the above command on the MySQL server, if the database named testdb no longer appears in the list, it means it has been successfully deleted:

Since the database testdb is not listed, it has been successfully dropped.

SQL DROP DATABASE with IF EXISTS Clause

If we try to delete a database that doesn't exist, SQL will throw an error. To avoid this, we can use the IF EXISTS clause with the DROP DATABASE command. This way, SQL will only try to delete the database if it actually exists.

Following is the syntax to create a database only if it does not already exist, using the IF NOT EXISTS clause in SQL:

Syntax

Following is the syntax to delete a database only if it exists, to avoid errors:

DROP DATABASE IF EXISTS database_name;

Example: If Database Exists

The following command deletes the testDB database only if it already exists:

DROP DATABASE IF EXISTS testDB;

Following is the output of the above code:

Query OK, 0 rows affected (0.05 sec)

Example: If Database Does Not Exist

Here, we are trying to delete the database testDB, which has already been deleted, and then run the same command again:

DROP DATABASE IF EXISTS testDB;

We get the following output:

Query OK, 0 rows affected, 1 warning (0.02 sec)

This warning indicates that the database did not exist. However, no error is thrown due to the IF EXISTS clause.

Deleting Multiple Databases

In SQL, you can delete multiple databases in a single command by separating their names with commas in the DROP DATABASE statement.

In MySQL Server Client, you cannot drop multiple databases at once using a single DROP DATABASE statement. If you do so, it will result in a syntax error. Each database must be dropped in its own statement.

However, in SQL Server, you can drop multiple databases at once by listing them in a comma-separated format.

Syntax

Following is the syntax to delete multiple databases using the DROP DATABASE statement in SQL Server:

DROP DATABASE db1, db2, db3;

Example

In this example, we are deleting three databases named testDB1, testDB2, and testDB3 at once in SQL Server:

DROP DATABASE testDB1, testDB2, testDB3;

We get the output as shown below:

Commands completed successfully.

When Should You Drop a Database?

You can use the DROP DATABASE command in the following situations:

• When you no longer need the database or project.
• When you are removing old test or staging (practice) databases.
• When you want to clean up your database server.
• When You are recreating the database from scratch after a major update or migration.