Home

	Expand Search

Reference Manual: Commands

Chapter 1 Commands

drop database

drop database

Description

Removes one or more databases from Adaptive Server.

Syntax

drop database database_name [, database_name] ...

Parameters

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

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

• 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 cannot use drop database to remove a damaged database. Use the dbcc dbrepair command:

  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.

See also

Commands

alter database, create database, dbcc, use

Procedures

sp_changedbowner, sp_helpdb, sp_renamedb, sp_spaceused