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
- Connect to the Database Engine in SSMS
- From the Standard bar, click "New Query".
- 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
- Click "Execute", your database will be deleted or droped
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:
- Open SSMS and then Connect to the Database Engine using your Credentials
- Expand "Databases"
- Right-Click on the database name that you want to delete and click on "Delete" menu item.
- 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)
- Then, click the "OK" button to delete the database.
- 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: