sp_dbremove System Stored Procedure

Used to remove a database, and optionally, any devices to which it has exclusive use. This system stored procedure is useful primarily with databases created on removable media; however, it can be used on all databases including damaged databases.

Syntax

sp_dbremove database[, dropdev]

where

database: Is the name of the database to be removed.
dropdev: Removes from sysdevices all devices of which that database has exclusive use. It does not delete the .DAT files.

Remarks

This stored procedure removes the entries in sysdatabases and sysusages for the specified database. If the dropdev keyword is included, it also removes from sysdevices all devices of which that database has exclusive use. It does not delete the .DAT files.

If no value is provided, the usage syntax is displayed.

Permission

Execute permission defaults to the system administrator and database owner of the database for which the drop will be performed.

Example

This example removes the sales database and removes from sysdevices all devices used exclusively by sales.

sp_dbremove sales, dropdev

Tables Used

master.dbo.sysalternates, master.dbo.spt_values, master.dbo.sysdatabases, master.dbo.sysprocesses