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.

SQL Server Enterprise Manager

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.


Practical LearningPractical Learning: Deleting a Database in the Enterprise Manager

  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

SQL Query Analyzer

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:


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.


Practical LearningPractical Learning: Deleting a Database With Code

  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
  5. Press F5 to execute the statement

Database Routines


The Current Database

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 LearningPractical Learning: Changing the Current Database

  1. To create a new database, delete the statement in the Query window and change the contents with:
  2. Execute the statement
  3. Replace the lines in the Query window with:
    USE Northwind
  4. Observer the currently selected database in the combo box of the toolbar.
    Press F5 to execute the statement
  5. Notice that the database in the combo box of the toolbar has changed
  6. Close SQL Query Analyzer and click Not To All to not save any of the current statements

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 LearningPractical Learning: Refreshing a List in the Enterprise Manager

  1. Make sure you are back in the SQL Server Enterprise Manager.
    Under the Databases node, notice that there is no database called WorldCupStats
  2. To refresh the list of databases, right-click Databases and click Refresh
  3. In the right frame, click NationalCensus
  4. Press and hold Ctrl
  5. Click WorldCupStats and release Ctrl
  6. Right-click one of the selected databases and click Delete
  7. Read the message and the label of the check box in the Delete Databases dialog box, then click OK
  8. Close SQL Server Enterprise Manager

Previous Copyright © 2005-2015, FunctionX Next