SHOW TABLES DROPPED

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

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