Drop Database in SQL Server

In this tutorial, we will show you how to Drop or delete a database in SQL Server is using the Drop Database statement and also using SQL Server Management Studio.

Create two database’s example database sales & purchase. You can refer to our previous tutorial How to Create Database in SQL. We will delete the sales database using the Drop Database statement. Delete the purchase database using the SQL Server Management Studio.

Using the Drop Database statement

The Syntax of the Drop Database is as shown below

For Exampe, to delete a sales database, use the command Drop Database sales

To Delete the Database

1. Connect the SQL Server using the SSMS
2. Open the Query editor by clicking on the Toolbar -> New Query
3.  Enter the Query Drop database Sales
4. Execute the query either by pressing F5 key or click on Execute option in the toolbar.
5. Commands completed Successfully message appears in the message window.
6. Refresh the object Explorer.

Drop Database Using SQL Server Management Studio

Another way to drop a database is by using the GUI tool of SSMS

1. Connect the SQL Server using the SSMS
2. Select the database to delete
3. Right Click and select Delete
4. In the Delete Object window, click OK to delete the database

Drop Multiple Database

You can drop multiple database in a single command. The syntax is

Drop Database <databaseName1>, <databaseName2>

Create two databases Sales & Purchase as shown below

You can drop both of them using

Drop Database if Exists

IF EXISTS Applies to: SQL Server 2016 and above

If you try to delete a non-existent database, you will see the following error message. For Example, run the command drop database hr and you will see the following error message

Cannot drop the database 'HR', because it does not exist or you do not have permission.

You can use the IF EXISTS Clause, which will not raise the error. For example, the following query will drop the database HR, only if exists else it does nothing

The database cannot be removed if in use

We cannot delete the database, which is in use. This means some user is reading or writing to the database.

For Example run the following query

The above example is a simple use case. You can run the use master to use the master database, which stops using the sales database. And then drop the sales database as shown below

But if you forcefully remove all the users, who are using the database using the command. SET SINGLE_USER forces the database be work in Single User mode. It logs out all other users. Now you can safely drop the database.

Reference

• Drop Database TSQL Command