SHOW TABLES DROPPED
Applies to: 
Databricks SQL Databricks Runtime 12.2 LTS and above
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
-
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
----------- ---------- ---------- ------- --------- ----------------------------- ----------------------------- ----------------------------- ------------- ------------ -------