SQL Server DROP DATABASE

2022-11-06T19:03:18.026Z

The SQL DROP DATABASE statement will delete the database permanently from the SQL Server Instance. Alternatively, we can delete the database using the SQL Management Studio interface.

Note: The most important things to keep in mind before executing the drop statement are:

The Database is deleted from the system and cannot be recovered unless there is a backup.
All the Physical files, contents, tables, views, stored procedures, etc that are associated with the database get deleted.
If the database is in use by an application or any database user, the drop statement throws an error during the database deletion. This means locks being held for reading or writing by any user.

Let us learn how to Delete a database in SQL with examples.

Syntax of DROP DATABASE

The Syntax for creating a new Database in SQL is

DROP DATABASE DB_NAME

We need to provide the valid database name that we would like to delete from the system to the DROP DATABASE SQL statement.

DROP DATABASE [ IF EXISTS ] { database_name } [ ,...n ] [;]

Starting from SQL Server 2016 (13.x) we can conditionally drop the database only if it already exists. You can also delete multiple databases using a single statement by passing the comma-separated values to the DROP DATABASE SQL statement.

Example 1: Drop Database in SQL

In the following example, we are deleting a database called “Employee" using the DROP DATABASE SQL statement.

DROP DATABASE Employee

Output

When we execute the above query the database gets deleted and shows the following output.

Commands completed successfully.

We can verify if the database is dropped in SQL using the following query. The SHOW DATABASES query will list all the databases in the system.

SHOW DATABASES;

Output

Tests
Student

Note: If we try to drop the database that does not exist, we will get an error message as shown below.

DROP DATABASE Employee

Output

Cannot drop database "Employee" because it is currently in use.

In order to avoid the error, we can use the conditional syntax if you are using SQL Server 2016 or above. This statement will delete the database only if it exists in the SQL Server instance.

DROP DATABASE  IF EXISTS  Employee

Output

Commands completed successfully.

Example 2: Drop the Database using SQL Server Management Studio

Deleting the Database through SSMS is straight forward. Login to SSMS and connect to the instance where the Database is present and follow the steps.

Step 1: Navigate to Object explorer, expand the databases section and find the database you want to delete.

Step 2: Right-Click on the Database you would like to delete and choose Delete from the contextual menu as shown below.

SQL Server Drop Database

Step 3: On Click of delete, you will get Delete Object window. Here you will get 2 options. Select Close existing connection and click ok.

Delete backup and restore history information for databases: Check this option if you would like to delete all the backups and restore history. This cannot be undone.
Close Existing Connections: Check this option as it will ensure if there are any existing connection it will close and then delete the database

SQL Server Drop Database

SQL Server Drop Database

Step 4: Verify that if you database is deleted in the Object Explorer’s Databases section.