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 statement 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) Delete 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)
