drop database

Description

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

Syntax

drop database database_name [, database_name] ...

Parameters

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:

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 

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.

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:

• Decrypt the columns or modify the columns for encryption by a different key using alter table, or

• Drop the table or database containing the encrypted columns

• 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:

See also