drop database

Syntax

drop database database_name [, database_name] ...

Parameters

database_name – is the name of a database to remove. Use sp_helpdb to get a list of databases.

Examples

Example 1 – Removes the publishing database and all its contents:
```
drop database publishing
```
Example 2 – key_db is the database where the encryption key resides and col_db is the database containing the encrypted columns. The SAP ASE server raises an error and fails to drop key_db. The drop of col_db succeeds. To drop both databases, drop col_db first:
```
drop database col_db, key_db 
```

Usage

When dropping an archive database, all the rows for that database are deleted from the sysaltusages table in the scratch database. This requires log space in the scratch database.
Removing a database deletes the database and all its objects, frees its storage allocation, and erases its entries from the sysdatabases and sysusages system tables in the masterdatabase.
drop database clears the suspect page entries pertaining to the dropped database from master..sysattributes.

When using encrypted columns and drop database, to prevent accidental loss of keys, drop database fails if the database contains keys currently used to encrypt columns in other databases. To drop a database:

Use alter table to decrypt the columns, or modify the columns for encryption using a different key.
Drop the table or database containing the encrypted columns.

See also sp_changedbowner, sp_helpdb, sp_renamedb, sp_spaceused in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for drop database differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be the database owner or have the `own database` privilege on the database. To drop `sybsecurity`, you must be the database owner or have the `manage auditing` privilege.
Disabled	With granular permissions disabled, you must be the database owner, a user with sa_role, or for `sybsecurity`, a user with sso_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	26
Audit option	drop
Command or access audited	drop database
Information in `extrainfo`	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if set proxy is in effect