DROP DATABASE

The DROP DATABASE statement deletes a database. Only the Master user can issue the statement.

DROP DATABASE database-name [ DELETE FILES ]

database-name ::= user-defined-name

As Master user, you must be logged on to a database to issue the statement. The DROP DATABASE statement can be used to drop any database, including the one to which you are currently logged on, provided the security setting permits deletion. See Secured Databasesbelow.

DROP DATABASE cannot be used to delete system databases such as defaultdb and tempdb. The statement can be used to delete the last remaining user-defined database if you choose, security permitting.

The DROP DATABASE statement cannot be used in a stored procedure or in a user-defined function.

Secured Databases

You cannot delete a database secured with the “database” transactional security model. See Available Models for the Transactional Interface in Advanced Operations Guide.

You can delete a database secured with any of the following ways:

•

The “classic” transactional security model

•

The “mixed” transactional security model

•

Relational security (Master password) in combination with “classic” or “mixed” transactional models

See Security Models and Concepts in Advanced Operations Guide.

The DELETE FILES clause specifies to delete the data dictionary files (DDFs). Data files are not deleted.

If DELETE FILES is omitted, the DDFs remain on physical storage, but the database name is deleted from dbnames.cfg. (Once the name is removed from dbnames.cfg, the database no longer exists to the database engine.) Retaining the DDFs allows you to re-create the database should you so choose.

Note that the DDFs must not be in use to delete them. If you have Pervasive PSQL Control Center open, for example, a “file is locked” error returns if you use the DELETE FILES clause. Having PCC open causes the DDFs to be in use, which prevents their deletion.

The following example deletes a database named “inventorydb” from dbnames.cfg, but retains the database’s DDFs (and data files) on physical storage.

DROP DATABASE inventorydb

============

The following example deletes a database named “HRUSBenefits” and its DDFs. Data files for HRUSBenefits are retained.

DROP DATABASE HRUSBenefits DELETE FILES

CREATE DATABASE