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

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 Databases below.

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.

The expression IF EXISTS causes the statement to return success instead of an error if a database does not exist. IF EXISTS does not suppress other errors.

You cannot delete a database secured with the Database security model. You can delete a database secured with any of the following ways:

For more information, see Zen Security in Advanced Operations Guide.

The DELETE FILES clause is for deleting 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 recreate the database should you so choose.

Note that the DDFs must not be in use to delete them. If you have Zen Control Center open, for example, a “file is locked” error returns if you use the DELETE FILES clause. While ZenCC is open, the DDFs are considered to be in use, which prevents their deletion.

The following example deletes a database named inventorydb from dbnames.cfg, but it retains the database DDFs (and data files) in physical storage.

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