Categories:

Database, Schema, & Share DDL

DROP DATABASE

Removes a database from the system.

See also:

CREATE DATABASE , SHOW DATABASES , UNDROP DATABASE

Syntax

DROP DATABASE [ IF EXISTS ] <name> [ CASCADE | RESTRICT ]

Parameters

name

Specifies the identifier for the database to drop. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

CASCADE | RESTRICT

Specifies whether the database can be dropped if foreign keys exist that reference any tables in the database:

  • CASCADE drops the database and all objects in the database, including tables with primary/unique keys that are referenced by foreign keys in other tables.

  • RESTRICT returns a warning about existing foreign key references and does not drop the database.

Default: CASCADE

Usage Notes

  • Dropping a database does not permanently remove it from the system. A version of the dropped database is retained in Time Travel for the number of days specified by the DATA_RETENTION_TIME_IN_DAYS parameter for the database:

    1. Within the Time Travel retention period, a dropped database can be restored using the UNDROP DATABASE command.

    2. When the Time Travel retention period ends, the next state for the dropped database depends on whether it is permanent or transient:

      • A permanent database moves into Fail-safe. In Fail-safe (7 days), a dropped database can be recovered, but only by Snowflake. When the database leaves Fail-safe, it is purged.

      • A transient database has no Fail-safe, so it is purged when it moves out of Time Travel.

    3. Once a dropped database has been purged, it cannot be recovered; it must be recreated.

  • After dropping a database, creating a database with the same name creates a new version of the database. The dropped version of the previous database can still be restored using the following method:

    1. Rename the current version of the database to a different name.

    2. Use the UNDROP DATABASE command to restore the previous version.

Database Replication Usage Notes

  • You can drop a secondary database at any time. Only the database owner (i.e. the role with the OWNERSHIP privilege on the database) can drop the database.

  • A primary database cannot be dropped if one or more replicas of the database (i.e. secondary databases) exist. To drop the primary database, first promote a secondary database to serve as the primary database, and then drop the former primary database. Alternatively, drop all of the secondary databases for the primary database, and then drop the primary database.

    Note that only the database owner can drop the database.

Examples

DROP DATABASE mytestdb2;

+---------------------------------+
| status                          |
|---------------------------------|
| MYTESTDB2 successfully dropped. |
+---------------------------------+

SHOW DATABASES LIKE 'mytestdb2';

+------------+------+------------+------------+--------+-------+---------+---------+----------------+
| created_on | name | is_default | is_current | origin | owner | comment | options | retention_time |
|------------+------+------------+------------+--------+-------+---------+---------+----------------|
+------------+------+------------+------------+--------+-------+---------+---------+----------------+

SHOW DATABASES HISTORY LIKE 'mytestdb2';

+---------------------------------+-----------+------------+------------+--------+--------+---------+---------+----------------+---------------------------------+
| created_on                      | name      | is_default | is_current | origin | owner  | comment | options | retention_time | dropped_on                      |
|---------------------------------+-----------+------------+------------+--------+--------+---------+---------+----------------+---------------------------------|
| Wed, 25 Feb 2015 16:16:54 -0800 | MYTESTDB2 | N          | N          |        | PUBLIC |         |         |              1 | Fri, 13 May 2016 17:35:09 -0700 |
+---------------------------------+-----------+------------+------------+--------+--------+---------+---------+----------------+---------------------------------+