drop database

Description

Removes one or more databases from Adaptive Server, including archive databases.

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

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 master database.
drop database clears the suspect page entries pertaining to the dropped database from master..sysattributes.

Encrypted columns and drop database

To prevent accidental loss of keys, Adaptive Server fails drop database if it contains keys currently used to encrypt columns in other databases. Before dropping the database containing the encryption keys you must first remove the encryption or drop the database containing the encrypted columns.

In the following example key_db is the database where the encryption key resides and col_db is the database containing the encrypted columns:

drop database key_db, col_db

Adaptive 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

Restrictions

You must be using the master database to drop a database.
You cannot drop a database that is in use (open for reading or writing by any user).
You cannot use drop database to remove a database that is referenced by a table in another database. Execute the following query to determine which tables and external databases have foreign-key constraints on primary key tables in the current database:
```
select object_name (tableid), frgndbname
    from sysreferences
    where frgndbname is not null
```
Use alter table to drop these cross-database constraints, then reissue the drop database command.
You can use drop database to remove a damaged database. If drop database does not run because the database is damaged, use the dbcc dbrepair to fix the database:
```
dbcc dbrepair (database_name, dropdb)
```
You cannot drop the sybsecurity database if auditing is enabled. When auditing is disabled, only the System Security Officer can drop sybsecurity.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Only the Database Owner can execute drop database, except for the sybsecurity database, which can be dropped only by the System Security Officer.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
26	drop	drop database	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 in effect