DROP TABLE

Removes a table from the current/specified schema, but retains a version of the table so that it can be recovered using UNDROP TABLE. For details, see Usage Notes (in this topic).

See also:

CREATE TABLE , ALTER TABLE , SHOW TABLES , TRUNCATE TABLE , DESCRIBE TABLE

Syntax

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

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_TIME_IN_DAYS parameter for the table:

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

    2. 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.

    3. 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
Language: English