Deleting or Dropping a Database in SQL: Step-by-Step Guide

The SQL DROP DATABASE statement is used to drop an existing database in SQL schema.

Syntax

The basic syntax of DROP DATABASE statement is as follows ?

DROP DATABASE DatabaseName;

Always the database name should be unique within the RDBMS.

Example

If you want to delete an existing database , then the DROP DATABASE statement would be as shown below ?

SQL> DROP DATABASE testDataBase;

NOTE ? Be careful before using this operation because by deleting an existing database would result in loss of complete information stored in the database.

Make sure you have the admin privilege before dropping any database. Once a database is dropped, you can check it in the list of the databases as shown below ?

SQL> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| AMROOD             |
| EMPLOYEE     |
| mysql              |
| orig               |
| test               |
+--------------------+
6 rows in set (0.00 sec)

SQL Server

To Delete or Drop a database

Code:

<span class="pln">
</span><span class="typ">Drop</span><span class="pln"> </span><span class="typ">Database</span><span class="pln"> </span><span class="typ">DatabaseThatYouWantToDrop</span><span class="pln">
</span>

Dropping a database, deletes the LDF and MDF files.

You cannot drop a database, if it is currently in use. You get an error stating - Cannot drop database "NewDatabaseName" because it is currently in use. So, if other users are connected, you need to put the database in single user mode and then drop the database.

Code:

<span class="pln">
</span><span class="typ">Alter</span><span class="pln"> </span><span class="typ">Database</span><span class="pln"> </span><span class="typ">DatabaseName</span><span class="pln"> </span><span class="typ">Set</span><span class="pln"> SINGLE_USER </span><span class="typ">With</span><span class="pln"> </span><span class="typ">Rollback</span><span class="pln"> </span><span class="typ">Immediate</span><span class="pln">
</span>

With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the database.

Note: System databases cannot be dropped.