DROP TABLE¶

Syntax¶

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

Parameters¶

name

Specifies the identifier for the table 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 (e.g. "My Object").

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

CASCADE | RESTRICT

Specifies whether the table can be dropped if foreign keys exist that reference the table:

• CASCADE drops the table even if the table has 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 table.

Default: CASCADE

Usage notes¶

• Dropping a table does not permanently remove it from the system. A version of the dropped table is retained in Time Travel for the number of days specified by the data retention period for the table:

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

  2. Changing the Time Travel retention period for the account or for a parent object (that is to say, database or schema) after a table is dropped does not change the Time Travel retention period for the dropped table. For more information, see the note in the Time Travel topic.

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

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

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

     Note

     A long-running Time Travel query will delay moving any data and objects (tables, schemas, and databases) in the account into Fail-safe, until the query completes. The purging of temporary and transient tables will similarly be delayed.

  4. Once a dropped table has been purged, it cannot be recovered; it must be recreated.

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

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

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

• Before dropping a table, verify that no views reference the table. Dropping a table referenced by a view invalidates the view (i.e. querying the view returns an “object does not exist” error).

• To drop a table, you must be using a role that has ownership privilege on the table.

Examples¶

Drop a table:

SHOW TABLES LIKE 't2%';

+---------------------------------+------+---------------+-------------+-----------+------------+------------+------+-------+--------------+----------------+
| created_on                      | name | database_name | schema_name | kind      | comment    | cluster_by | rows | bytes | owner        | retention_time |
|---------------------------------+------+---------------+-------------+-----------+------------+------------+------+-------+--------------+----------------+
| Tue, 17 Mar 2015 16:48:16 -0700 | T2   | TESTDB        | PUBLIC      | TABLE     |            |            |    5 | 4096  | PUBLIC       |              1 |
+---------------------------------+------+---------------+-------------+-----------+------------+------------+------+-------+--------------+----------------+

DROP TABLE t2;

+--------------------------+
| status                   |
|--------------------------|
| T2 successfully dropped. |
+--------------------------+

SHOW TABLES LIKE 't2%';

+------------+------+---------------+-------------+------+---------+------------+------+-------+-------+----------------+
| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time |
|------------+------+---------------+-------------+------+---------+------------+------+-------+-------+----------------|
+------------+------+---------------+-------------+------+---------+------------+------+-------+-------+----------------+

Copy

Drop the table again, but don’t raise an error if the table does not exist:

DROP TABLE IF EXISTS t2;

+------------------------------------------------------------+
| status                                                     |
|------------------------------------------------------------|
| Drop statement executed successfully (T2 already dropped). |
+------------------------------------------------------------+

Copy