SQL DROP DATABASE Statement

The SQL DROP DATABASE Statement

TheDROP DATABASEstatement 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 theDROP DATABASEcommand. 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 theDROP DATABASEstatement 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 namedtestDB:

DROP DATABASE testDB;

After executing the above command inMySQL 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 namedtestdbno longer appears in the list, it means it has been successfully deleted:

Databaseinformation_schemalibrarymysqlperformance_schemasys

Since the databasetestdbis 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 theIF EXISTSclause with theDROP DATABASEcommand. 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 theIF NOT EXISTSclause 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 thetestDBdatabase 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 databasetestDB, 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 theIF EXISTSclause.

Deleting Multiple Databases

In SQL, you can delete multiple databases in a single command by separating their names with commas in theDROP DATABASEstatement.

InMySQL Server Client, you cannot drop multiple databases at once using a singleDROP DATABASEstatement. If you do so, it will result in a syntax error. Each database must be dropped in its own statement.However, inSQL 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 theDROP DATABASEstatement in SQL Server:

DROP DATABASE db1, db2, db3;

Example

In this example, we are deleting three databases namedtestDB1,testDB2, andtestDB3at 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 theDROP DATABASEcommand 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.