Database Maintenance: Deleting a Database

If you have created a database but don't need it anymore, you can delete it. It is important to know, regardless of how you create a database, whether using SQL Server Enterprise Manager, SQL Query Analyzer, the Command Prompt, or Visual Studio .NET, every database can be access by any of these tools and you can delete any of the databases using any of these tools.

As done with creating a database, every tool provides its own means.

To delete a database in SQL Server Enterprise Manager, locate it in either the left or the right frames, right-click it and click Delete. You would receive a message box asking for a confirmation. If you still want to delete the database, you can click Yes.

1. Return to the SQL Server Enterprise Manager
2. Under the Databases node, right-click PublicProperties and click Delete
3. Read the lines on the message box and click Yes

To delete a database in SQL Query Analyzer, you use the DROP DATABASE instruction followed by the name of the database. The formula used is:

DROP DATABASE DatabaseName

Before deleting a database in SQL, you must make sure the database is not being used or accessed by some one else or by another object.

1. Return to the SQL Query Enterprise Analyzer
2. To generate a new blank Query window, on the main menu of SQL Query Analyzer, click File -> New...
3. In the New dialog box, accept the Blank Query Window selection and click OK 
4. To delete a database, type:
    

   DROP DATABASE MotorVehicleDivision GO

5. Press F5 to execute the statement

While writing code in a Query Window of the SQL Query Analyzer, you should always know what database you are working on, otherwise you may add code to the wrong database. To programmatically specify the current database, type the USE keyword followed by the name of the database. Here is an example:

USE WorldSeries

There is another technique to specify the database you want to work with. On the toolbar, click the arrow of the combo box and select the database you want to use.

Alternatively, if you don't know for sure what database you want to select or you don't even know what databases exist on your server, on the main menu, you can click Query -> Change Database... This would display the Select Database dialog box:

You can click the desired database and click OK.

Whether you use the USE keyword or the combo box, once you have selected your database, unless specified otherwise, any instruction you give in the Query window would apply to the selected database.

Practical Learning: Changing the Current Database

Refreshing the List of Databases

Some of the windows that display databases, like the SQL Server Enterprise Manager, don't update their list immediately if an operation occurred outside their confinement. For example, if you create a database in SQL Query Analyzer, its name would not be updated in the Databases node of Enterprise Manager. To view such external changes, you can refresh the window that holds the list.

In SQL Server Enterprise Manager, to update a list, you can right-click its category in the left frame and click Refresh. Only that category may be refreshed.

Practical Learning: Refreshing a List in the Enterprise Manager