DROP TABLE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Deletes the table and removes the directory associated with the table from the file system if the table is not an EXTERNAL table. An exception is thrown if the table does not exist. To drop a table you must have the MANAGE privilege on the table, be its owner, or the owner of the schema, catalog, or metastore the table resides in.

In the case of an external table, only the associated metadata information is removed from the metastore schema.

Any foreign key constraints referencing the table are also dropped.

If the table is cached, the command uncaches the table and all of its dependents.

Note

When you drop a managed table in Unity Catalog, the underlying data files are retained to allow for a 7-day recovery window. Use the UNDROP command within this window to recover the table. After the recovery window ends, the data files are permanently deleted (purged) within 48 hours. For more about the data file lifecycle, billing, and recovery, see Object storage lifecycle in Unity Catalog.

Syntax

DROP TABLE [ IF EXISTS ] table_name [ FORCE ]
DROP {TEMP | TEMPORARY} TABLE table_name [IF EXISTS]

Parameter

  • TEMP or TEMPORARY

    Applies to: check marked yes Databricks SQL

    If specified, drops the temporary table. If no temporary table with this name exists in the session, the command raises a TABLE_OR_VIEW_NOT_FOUND error condition error.

    If not specified, the command drops only a permanent table and raises an error if a temporary table with the same unqualified name exists in the session. To drop a permanent table when a temporary table shares the same name, use the qualified name.

  • IF EXISTS

    If specified, no TABLE_OR_VIEW_NOT_FOUND error is thrown when the table does not exist.

  • table_name

    The name of the table to be dropped. The name must not include a temporal specification or options specification. If the table cannot be found Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error. If the relation found is not a table Azure Databricks raises a WRONG_COMMAND_FOR_OBJECT_TYPE error.

  • FORCE

    If specified, the table can be dropped even if it has dependent shallow clones. This is only applicable for Unity Catalog managed tables. See Drop the base table for a shallow clone.

Examples

-- Assumes a table named `employeetable` exists.
> DROP TABLE employeetable;

-- Assumes a table named `employeetable` exists in the `userdb` schema
> DROP TABLE userdb.employeetable;

-- Assumes a table named `employeetable` does not exist.
-- Throws TABLE_OR_VIEW_NOT_FOUND
> DROP TABLE employeetable;
  Error: TABLE_OR_VIEW_NOT_FOUND

-- Assumes a table named `employeetable` does not exist. Try with IF EXISTS
-- this time it will not throw exception
> DROP TABLE IF EXISTS employeetable;

-- Assumes a table named `employeetable` exists and has a shallow clone.
-- Throws CANNOT_DROP_BASE_TABLE_REFERENCED_BY_SHALLOW_CLONE
> DROP TABLE employeetable;
  Error: CANNOT_DROP_BASE_TABLE_REFERENCED_BY_SHALLOW_CLONE

-- Assumes a table named `employeetable` exists and has a shallow clone.
-- Drops base table, but shallow clones referencing this base table will no longer work
> DROP TABLE employeetable FORCE;

-- Creates and drops a temporary table
> CREATE TEMPORARY TABLE scratchpad(txt STRING);
> DROP TEMPORARY TABLE scratchpad;
  • Last updated on