Share via

SHOW TABLES DROPPED

  • Article

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

Important

This feature is in Public Preview.

This command lists all tables which have been dropped within the schema in Unity Catalog, but can still be undropped. Specifically, it lists all dropped tables within the retention period (default is 7 days). If the schema or catalog have been dropped, an error is raised. If no schema is specified then the tables are returned from the current schema.

The command will only list tables which the requester is able to UNDROP. Consequently, metastore/catalog/schema owners have privileges to list all dropped tables within their respective securable ownership. Users with table level ownership will only be able to view tables which they own within the input catalog/schema as long as they have the USE CATALOG privilege on the parent catalog and the USE SCHEMA privilege on the parent schema.

Syntax

SHOW TABLES DROPPED [ { FROM | IN } schema_name ] [ LIMIT maxResults ]

Parameters

  • schema_name

    Specifies schema name from which tables are to be listed. If not provided, uses the current schema. If the schema or catalog does not exist or has been dropped, a SCHEMA_NOT_FOUND error is raised.

  • maxResult

    An integer literal limiting the number of tables returned.

Returns

The command s produces a report of the list of tables with the following columns:

Name Data Type Nullable Description”
catalogName STRING no The catalog name of the listed table.
schemaName STRING no The schema name of the listed table.
tableName STRING no The name of the dropped table
tableId STRING no The table ID that can be used to identify and undrop a specific version of the dropped table.
tableType STRING no The type of the dropped table in Unity Catalog
deletedAt STRING no The time when the table was dropped.
createdAt STRING no The time when the table was created.
updatedAt STRING no The time when the table was last updated.
createdBy STRING no The principal who created the table.
owner STRING no The principal who owns the table.
comment STRING yes The optional table comment.

Examples

— List dropped tables from an existing schema + catalog.
> USE CATALOG default;
> USE SCHEMA my_schema;
> CREATE TABLE my_table_1;
> CREATE TABLE my_table_2;
> DROP TABLE my_table_1;
> SHOW TABLES DROPPED;
  catalogname schemaname tablename  tableid tabletype deletedat                     createdat                     updatedat                     createdby     owner         comment
  ----------- ---------- ---------- ------- --------- ----------------------------- ----------------------------- ----------------------------- ------------- ------------- -------
  default     my_schema  my_table_1 <uuid>  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

-- Create a new table with name `my_table_1` since other was dropped.
> CREATE TABLE my_table_1;
> DROP TABLE my_table_1;
> SHOW TABLES DROPPED IN default.my_schema;
  catalogname schemaname tablename  tableid tabletype deletedat                     createdat                     updatedat                     createdby     owner         comment
  ----------- ---------- ---------- ------- --------- ----------------------------- ----------------------------- ----------------------------- ------------- ------------- -------
  default     my_schema  my_table_1 <uuid>  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

— List dropped tables when some are past the retention period.
> USE CATALOG default;
> USE SCHEMA my_schema;
> CREATE TABLE my_table_1;
> DROP TABLE my_table_1;
-- Wait 8 days (1 more than 7 day retention period)
> SHOW TABLES DROPPED;
  catalogname schemaname tablename  tableid tabletype deletedat                     createdat                     updatedat                     createdby     owner        comment
  ----------- ---------- ---------- ------- --------- ----------------------------- ----------------------------- ----------------------------- ------------- ------------ -------