Skip to main content

DROP DATABASE (SQL)

Deletes a database (namespace).

Synopsis

DROP DATABASE [IF EXISTS] dbname [RETAIN_FILES]

Description

The DROP DATABASE command deletes a namespace and its associated database.

The specified dbname is the name of the namespace and the directory that contains the corresponding database files. Specify dbname as an identifier. Namespace names are not case-sensitive. If the specified dbname namespace does not exist, InterSystems IRIS issues an SQLCODE -340 error.

The DROP DATABASE command is a privileged operation. Prior to using DROP DATABASE, it is necessary to be logged in as a user with the %Admin_Manage resource. The user must also have READ permission on the resource for the routines and global's database definitions. Failing to do so results in an SQLCODE -99 error (Privilege Violation).

Use the $SYSTEM.Security.Login()Opens in a new tab method to assign a user with appropriate privileges:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, see %SYSTEM.SecurityOpens in a new tab.

DROP DATABASE cannot be used to drop a system namespace, regardless of privileges. Attempting to do so results in an SQLCODE -342 error.

DROP DATABASE cannot be used to drop the namespace that you are currently using or connected to. Attempting to do so results in an SQLCODE -344 error.

You can also delete a namespace using the Management Portal. Select System Administration, Configuration, System Configuration, Namespaces to list the existing namespaces. Click the Delete button for the namespace you wish to delete.

RETAIN_FILES

If you specify this option, the physical file structure is retained; the database and its associated namespace is removed. After performing this operation, a subsequent attempt to use dbname results in the following:

  • DROP DATABASE without RETAIN_FILES cannot remove this physical file structure. Instead, it results in an SQLCODE -340 error (Database not found).

  • DROP DATABASE with RETAIN_FILES also results in an SQLCODE -340 error (Database not found).

  • CREATE DATABASE cannot create a new database with the same name. Instead, it results in an SQLCODE -341 error (Cannot create database file for database).

  • Attempting to use this namespace results in a <NAMESPACE> error.

Server Init and Disconnect Codes

The Server Init Code and Server Disconnect Code can be assigned to a namespace using the $SYSTEM.SQL.Util.SetOption("ServerInitCode",value)Opens in a new tab and $SYSTEM.SQL.Util.SetOption("ServerDisconnectCode",value)Opens in a new tab methods. The current values can be determined using the corresponding $SYSTEM.SQL.Util.GetOption()Opens in a new tab method options.

Deleting a namespace, using DROP DATABASE or other interfaces, deletes these Server Init Code and Server Disconnect Code values. Therefore, deleting and then re-creating a namespace will require you to re-specify these values.

Arguments

IF EXISTS

An optional argument that, if specified, suppresses the error if the command is executed on a nonexistent database.

dbname

The name of the database (namespace) to be deleted.

RETAIN_FILES

An optional argument that, if specified, the physical database files (IRIS.DAT files) will not be deleted. The default is to delete the .DAT files along with the namespace and the other database entities.

Example

The following example deletes a namespace and its associated database (in this case 'c:\InterSystems\IRIS\mgr\DocTestDB'). It retains the physical database files:

CREATE DATABASE DocTestDB ON DIRECTORY 'c:\InterSystems\IRIS142\mgr\DocTestDB'
DROP DATABASE DocTestDB RETAIN_FILES

See Also

FeedbackOpens in a new tab