SQL Server DROP Database

Sometimes a database is not relevant or obsolete on our server instance. In that case, we can delete or remove them from the server. When we remove the database, it will also permanently delete all the tables, indexes, and constraints. Therefore, we should have to be very careful while dropping the database because we will lose all the data available in the database. If the database is not available on the server instance, SQL Server will throw an error message.

We can drop a database in SQL Server mainly in two ways:

• Transact-SQL Command
• SQL Server Management Studio

Let us discuss them one by one in detail.

DROP database using T-SQL Command

SQL Server enables users to remove the database from the server instance by using the below syntax:

If we want to delete more than one database within a single command, we can use the comma-separated list of database names as below:

In this syntax, the database_name indicates the name of a database we want to remove from the server instance. The IF EXIST is an optional clause indicating the database will be deleted if it already exists in the server.

We must remember the following points before deleting a database:

1. The DROP DATABASE command will delete the database as well as the database's physical disc files. As a result, we are required to keep a database backup if we need to recover it in the future.
2. The DROP DATABASE statement should be run in auto-commit mode. It cannot be used in a transaction, either explicit or implicit, as auto-commit is the default transaction management mode.
3. We are unable to delete the currently active database. If we will do this, SQL Server issues the following error:

Example

Let us understand how to delete a database, which is no longer required on the server instance. First, we will list all databases stored in the database engine by using the following command:

Executing the statement will display the list of all databases:

If we want to delete a 'DemoDb' database, we can use the statement as follows:

After executing the command, click the refresh button or pressing the F5 on our keyboard to update the object list. We can see that the DemoDb database is no longer available:

DROP Database using SQL Server Management Studio

We can drop a database in SSMS with the help of the following steps:

Step 1: Open the SSMS in administrator mode to avoid any permission issue. We will see the below screen where we need to click on Connect button to continue.

Step 2: Once the connection becomes successful, the Object Explorer window will appear on the left-hand side of the screen. To see the Database folder, click the "+" button if it isn't extended.

Step 3: The next step is to expand the Databases folder by clicking on the plus(+) button. We will see all available databases in the current server instance.

Step 4: The next step is to delete the desired database. Suppose we want to delete the DemoDb database. To do this, right-click on the selected database and choose the Delete option from the context menu. See the below image:

Step 5: Once we click the Delete option, we will see a Delete Model Object box to confirm the deletion process. Click on the OK button to delete the database from the SQL Server permanently.

Step 6: If everything is correct, the selected database will be deleted. We can verify this by clicking the refresh button or pressing the F5 on our keyboard to update the object list. We can see that the DemoDb database is no longer available: