Professional SQL Server™ 2005 Programming

4.4. The DROP Statement

Performing a DROP is the same as deleting whatever object(s) you reference in your DROP statement. It's very quick and easy, and the syntax is exactly the same for all of the major SQL Server objects (tables, views, sprocs, triggers, etc.). It goes like this:

DROP <object type> <object name> [, ...n]

Actually, this is about as simple as SQL statements get.

USE Accounting

DROP TABLE Customers

Poof — gone.

NOTE

Be very careful with this command. There is no, "Are you sure?" kind of question that goes with this — it just assumes you know what you're doing and deletes the object(s) in question.

The syntax is very much the same for dropping the entire database. Now drop the Accounting database:

USE master

DROP DATABASE Accounting

You should see the following in the Results pane:

Deleting database file 'c:\Program Files\Microsoft SQL Server\mssql\data\AccountingLog.ldf'.
Deleting database file 'c:\Program Files\Microsoft SQL Server\mssql\data\AccountingData.mdf'.

You may run into a situation where you get an error that says that the database cannot be deleted because it is in use. If this happens, check a couple of things:

• Make sure that the database that you have as current in the Management Studio is something other than the database you're trying to drop (that is, make sure you're not using the database as you're trying to drop it).

• Ensure that you don't have any other connections open (using the Management Studio or sp_who) that are showing the database you're ...