Categories:
Database, Schema, & Share DDL

DROP SCHEMA

Removes a schema from the current/specified database.

See also:
CREATE SCHEMA , SHOW SCHEMAS , UNDROP SCHEMA

Syntax

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

Parameters

name

Specifies the identifier for the schema 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.

If the schema identifier is not fully-qualified (in the form of db_name.schema_name), the command looks for the schema in the current database for the session.

CASCADE | RESTRICT

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

  • CASCADE drops the schema and all objects in the schema, 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 schema.

Default: CASCADE

Usage Notes

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

    1. Within the Time Travel retention period, a dropped schema can be restored using the UNDROP SCHEMA command.
    2. When the Time Travel retention period ends, the next state for the dropped schema depends on whether it is permanent or transient:
      • A permanent schema moves into Fail-safe. In Fail-safe (7 days), a dropped schema can be recovered, but only by Snowflake. When the schema leaves Fail-safe, it is purged.
      • A transient schema has no Fail-safe, so it is purged when it moves out of Time Travel.
    3. Once a dropped schema has been purged, it cannot be recovered; it must be recreated.
  • After dropping a schema, creating a schema with the same name creates a new version of the schema. The dropped version of the previous schema can still be restored using the following method:

    1. Rename the current version of the schema to a different name.
    2. Use the UNDROP SCHEMA command to restore the previous version.

Examples

Drop a schema named myschema (from the CREATE SCHEMA examples):

DROP SCHEMA myschema;

+--------------------------------+
| status                         |
|--------------------------------|
| MYSCHEMA successfully dropped. |
+--------------------------------+

SHOW SCHEMAS;

+---------------------------------+--------------------+------------+------------+---------------+--------+-----------------------------------------------------------+---------+----------------+
| created_on                      | name               | is_default | is_current | database_name | owner  | comment                                                   | options | retention_time |
|---------------------------------+--------------------+------------+------------+---------------+--------+-----------------------------------------------------------+---------+----------------|
| Fri, 13 May 2016 17:26:07 -0700 | INFORMATION_SCHEMA | N          | N          | MYTESTDB      |        | Views describing the contents of schemas in this database |         |              1 |
| Tue, 17 Mar 2015 16:57:04 -0700 | PUBLIC             | N          | Y          | MYTESTDB      | PUBLIC |                                                           |         |              1 |
+---------------------------------+--------------------+------------+------------+---------------+--------+-----------------------------------------------------------+---------+----------------+