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.

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 |
+---------------------------------+-----------+------------+------------+--------+--------+---------+---------+----------------+---------------------------------+