In previous question, I have mentioned answer for Check if table exists then delete it in SQL Server but now in this article, I have explained step by step procedure to delete or drop database in SQL Server using SSMS (SQL Server Management Studio) and to drop sql server database using SQL Query.

Drop Database using SQL Query

Usually, the syntax to drop/delete any database using SQL Query is as below

Use Master
DROP DATABASE  [ IF EXISTS ]
    database_name 
    [,database_name2,...];

In the above query, IF EXISTS option is available from SQL Server 2016 (13.x).

It allows you to conditionally delete a database only if the database already exists. 

So here are the basic steps to delete or drop database using SQL Query

  1. Connect to the Database Engine in SSMS
  2. From the Standard bar, click "New Query".
  3. Copy and paste the following example into the query window, for example, if you want to delete database with name "TestSumJoin", your query to drop this database would be
    Use Master
DROP DATABASE TestSumJoin
  4. Click "Execute", your database will be deleted or droped

Output:

drop-database-sql-server-query

In the above query, we are not using "If Exists" and considering database exists.

To Drop Multiple database at once using SQL Query, you can seperate it ","

DROP DATABASE TestSumJoin, TestSumJoin2;

Drop or delete database using SSMS(SQL Server Management Studio) GUI

If there is issue in running above query and deleting database, then you can also SSMS GUI, to delete the database.

Here are the steps to follow:

  1. Open SSMS and then Connect to the Database Engine using your Credentials
  2. Expand "Databases"
  3. Right-Click on the database name that you want to delete and click on "Delete" menu item.
    drop-database-sql-server-management-studio
  4. A pop-up will appear, from there, uncheck the "Delete backup and restore history information for database"  and select the "Close existing connections" (So it can delete database, even if there is an open connection for database)
    delete-database-sql-server
  5. Then, click the "OK" button to delete the database.
  6. Now, can refresh "Databases" in SSMS, you will see that database doesn't exists anymore

That's it, hope it helped.

You may also like to read:

Drop all tables of SQL Server database

Check database size in Sql server ( Various Ways explained)

How to list all stored procedures in SQL Server

Creating SQL Server Database using SQL Server Management Studio

How to check SQL server version

Understanding SQL server switch case (With Example)

Import data from Excel to SQL Server

SQL server date format and converting it (Various examples)

Difference between Stored procedure and function in SQL Server

How to convert nvarchar column to datetime in SQL Server?