Removes one or more databases from Adaptive Server, including archive databases.
drop database database_name [, database_name] ...
is the name of a database to remove. Use sp_helpdb to get a list of databases.
Removes the publishing database and all its contents:
drop database publishing
key_db is the database where the encryption key resides and col_db is the database containing the encrypted columns. 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
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:
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.
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. Determine which tables and external databases have foreign-key constraints on primary key tables in the current database, execute:
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 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.
ANSI SQL – Compliance level: Transact-SQL extension.
The permission checks for drop database differ based on your granular permissions settings.
Granular permissions 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. |
Granular permissions disabled |
With granular permissions disabled, you must be the database owner, a user with sa_role, or for sybsecurity, a user with sso_role. |
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
26 |
drop |
drop database |
|
Commands alter database, create database, dbcc, use
Procedures sp_changedbowner, sp_helpdb, sp_renamedb, sp_spaceused
