SHOW TABLES DROPPED

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

Preview

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