UNDROP TABLE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above

The UNDROP command addresses the concern of managed or external tables located in Unity Catalog being accidentally dropped or deleted. By default, this command undrops (recovers) the most recently dropped table owned by the user of the given table name. The parent schema and catalog must exist. This feature supports recovering dropped tables within a 7 day retention period.

If there are multiple dropped tables of the same name, you can use SHOW TABLES DROPPED to identify the table ID and use UNDROP TABLE WITH ID to recover a specific table.

If there is a table with the same name as the table you wish to recover, use ALTER TABLE RENAME TO command to change the name of the existing table.

Table metadata – such as table privileges, column spec, and properties – will be recovered. Primary and foreign key constraints are not recovered by the UNDROP command. Recreate them manually using ALTER TABLE ADD CONSTRAINT after the table has been recovered.

Syntax 

UNDROP TABLE { table_name | WITH ID table_id }

Parameter

Permissions

UNDROP TABLE requires one of the following base permissions:

  • A user is the owner of the table, has CREATE TABLE and USE SCHEMA on the schema, and USE CATALOG on the catalog.

  • A user is the owner of the schema and has USE CATALOG on the catalog.

  • A user is the owner of the catalog.

  • A user is the owner of the metastore.

If a user is recovering a different type of table, additional permissions apply. For example, to undrop an external table, you must also have CREATE EXTERNAL TABLE on the external location or storage credential, which must exist.

After running this command, the ownership defaults to the previous table owner. If required, the ownership can be changed using the ALTER TABLE command.

Examples 

-- UNDROP using the table name
> CREATE TABLE my_catalog.my_schema.my_table (id INT, name STRING);
> DROP TABLE my_catalog.my_schema.my_table;
> UNDROP TABLE my_catalog.my_schema.my_table;
  OK

-- UNDROP WITH ID
– Use SHOW TABLES DROPPED to find dropped tables
> SHOW TABLES DROPPED IN my_schema;
  catalogname schemaname tablename  tableid                              tabletype deletedat                     createdat                     updatedat                     createdby     owner         comment
  ----------- ---------- ---------- ------------------------------------ --------- ----------------------------- ----------------------------- ----------------------------- ------------- ------------- -------
  my_catalog  my_schema  my_table   6ca7be55-8f58-47a7-85ee-7a59082fd17a managed   2023-05-03 AD at 18:17:56 UTC 2023-05-03 AD at 18:17:00 UTC 2023-05-03 AD at 18:17:00 UTC alf@melmak.et alf@melmak.et
  my_catalog  my_schema  my_table   b819f397-c51f-4e60-8acc-05d4d4a7e084 managed   2023-05-04 AD at 10:20:00 UTC 2023-05-04 AD at 08:20:00 UTC 2023-05-04 AD at 08:20:00 UTC alf@melmak.et alf@melmak.et

–- Undrop a specific dropped table.
–- Here, we undrop my_table with table id '6ca7be55-8f58-47a7-85ee-7a59082fd17a'.
-- Note that the table id will be a string surrounded by single quotation marks.
> UNDROP TABLE WITH ID '6ca7be55-8f58-47a7-85ee-7a59082fd17a';
  OK

– Continuing from the example above, Now we want to undrop table with ID 'b819f397-c51f-4e60-8acc-05d4d4a7e084'.
- First, we rename the existing table
> ALTER TABLE my_table RENAME TO my_other_table
  OK
- Then we can undrop table with the name my_table
> UNDROP TABLE WITH ID 'b819f397-c51f-4e60-8acc-05d4d4a7e084'
  OK