SQL – DROP or DELETE Database – Tutorialspoint

The DROP DATABASE statement in SQL is used to delete a database along with all data, such as tables, views, indexes, stored procedures, and constraints.

The following are the most important points to remember before attempting

to delete the database: It is important to make sure that we have to back up the database

• that we are going to delete because once the “DROP DATABASE” statement is executed, all database data and objects in the database will be permanently deleted and cannot be recovered.
• It is also important to make sure that no other user or application is currently connected to the database we want to delete. If we try to delete the database while other users are connected to it, it may cause data corruption or other problems.

Note: Make sure that you have the necessary privilege before deleting any

database by using the DROP DATABASE statement.

Syntax

The following is the syntax for deleting a database in SQL:

DROP DATABASE DatabaseName;

Here, the “DatabaseName” is the name of the database we want to delete.

Always the database name must be unique within the RDBMS.

Example

First, let’s create multiple databases in the database system using the following query –

SQL> CREATE DATABASE testDB1; CREATE TESTDB2 DATABASE; CREATE TESTDB3 DATABASE; CREATE TESTDB4 DATABASE;

Let’s check whether or not databases are created using the following query −

SQL> SELECT* FROM SYS. DATABASES +-+ | Database | +-+ | Master | | tempdb | | Model | | msdb | | testDB1 | | testDB2 | | testDB3 | | testDB4 | +-+ 8 rows together (0.00 sec)

Now, let’s try to delete a single existing database <testDB> using the DROP

DATABASE − SQL> DROP DATABASE testDB1 statement;

Once we have deleted the <testDB> database, we can check whether or not it is deleted using the following query:

SQL> SELECT * FROM sys.databases; +-+ | Database | +-+ | Master | | tempdb | | Model | | msdb | | testDB2 | | testDB3 | | testDB4 | +-+ 7 rows together (0.00 sec)

That’s it! we have successfully deleted

a database in SQL.

Example

Now, let’s try to delete a database that does not exist in the database system:

SQL> DROP DATABASE testDB1;

The above query returns an error declaration because the database we are trying to delete

does not exist in the database system: Unable to remove database ‘testDB1’, because it does not exist or does not have permission.

DROP Statement

DATABASE IF EXISTS

The DROP DATABASE IF EXISTS statement in SQL includes a condition to check whether the database exists before attempting to delete it. If the database does not exist in the database system, the “DROP DATABASE IF EXISTS” statement does not generate an error, but simply terminates without taking any action.

Syntax

The following is the syntax for the DROP DATABASE IF EXISTS statement in SQL: DROP DATABASE IF EXISTS

DatabaseName;

Here, the “DatabaseName” is the name of the database we want to delete.

Example

Let’s try to delete an existing database <testDB2> in the database system using the following query statement:

SQL> DROP DATABASE IF EXISTS testDB2;

When running the given program, the output is displayed as follows:

The commands completed successfully.

Let’s check if the database <testDB2> is deleted or not using the following query −

SQL> SELECT* FROM SYS. DATABASES +-+ | Database | +-+ | Master | | tempdb | | Model | | msdb | | testDB3 | | testDB4 | +-+ 6 rows together (0.00 sec) Remove the database that does not exist

If you try to remove

the database that does not exist

Example

Let’s try to delete a database <testDB2> that does not exist in the database system by using the following query statement −

SQL> DROP DATABASE IF EXISTS testDB2;

When we run the previous program, the output is obtained as follows:

The commands were completed successfully.

Deleting

multiple databases You can also remove multiple databases

at once by using the DROP DATABASE statement.

Example

Let’s try to delete multiple databases from the database system using the following query:

SQL> DELETE DATABASE TESTDB3, testDB4; Once we have deleted the

databases, we can check whether they are deleted in the database system or not using the following query:

SQL> SELECT * FROM sys.databases; +-+ | Database | +-+ | Master | | tempdb | | Model | | msdb | +-+ 4 rows together (0.00 sec)