DROP DATABASE Statement

Removes one or more databases from SQL Server.

Syntax

DROP DATABASE database_name [, database_name...]

where

database_name

Specifies the database to be removed. Execute the sp_helpdb system stored procedure from the master database to see a list of databases.

Remarks

Removing a database deletes the database and all its objects, frees its storage allocation, and erases references to it in the master database. A database that has been dropped can only be re-created from database backups. You cannot drop a database that is in use (open for reading or writing by any user).

In earlier releases, the DROP DATABASE statement would not work on damaged databases. In SQL Server 6.0, the DROP DATABASE statement works on databases marked suspect, offline, or not recovered. To drop a database in recovery (sysdatabases..status = 128), use the sp_dbremove system stored procedure.

After any changes are made to the master database, use the DUMP DATABASE statement to back up the master database.

Permission

DROP DATABASE permission defaults to the database owner and cannot be transferred. The database owner must be in the master database to use DROP DATABASE.

Examples

A.    Drop a Single Database

This example removes all references for the publishing database from the system tables.

DROP DATABASE publishing

B.    Drop Multiple Databases

This example removes all references for each of the listed databases from the system tables.

DROP DATABASE pubs, newpubs

See Also