SHOW VOLUMES DROPPED

Applies to: Databricks SQL Databricks Runtime 19.11 and above Unity Catalog only

SHOW VOLUMES DROPPED lists the dropped volumes in a Unity Catalog schema that can still be recovered.

If no schema is specified, volumes are listed from the current schema. If the schema or catalog does not exist, an error is raised.

Syntax

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

Parameters

• schema_name

  Specifies the schema from which dropped volumes are listed. If not provided, uses the current schema.

• maxResults

  An integer literal limiting the number of volumes returned.

Permissions

The command lists only volumes that the requester can recover with UNDROP VOLUME. A user must own a metastore, catalog, or schema (or have the MANAGE privilege on schema) to list all dropped tables within that securable. A user who owns a dropped volume can list that volume if they have the USE CATALOG privilege on the parent catalog and the USE SCHEMA privilege on the parent schema.

Returns

The command produces a report of dropped volumes with the following columns:

Name	Data Type	Nullable	Description
catalog	STRING	no	The catalog name of the dropped volume.
schema	STRING	no	The schema name of the dropped volume.
volume_name	STRING	no	The name of the dropped volume.
volume_id	STRING	no	The volume ID used to identify and undrop a specific dropped volume with UNDROP VOLUME WITH ID.
owner	STRING	no	The principal who owns the volume.
comment	STRING	yes	The optional volume comment.
created_at	STRING	no	The time when the volume was created.
created_by	STRING	no	The principal who created the volume.

Name	Data Type	Nullable	Description
catalog	STRING	no	The catalog name of the dropped volume.
schema	STRING	no	The schema name of the dropped volume.
volume_name	STRING	no	The name of the dropped volume.
volume_id	STRING	no	The volume ID used to identify and undrop a specific dropped volume with UNDROP VOLUME WITH ID.
owner	STRING	no	The principal who owns the volume.
comment	STRING	yes	The optional volume comment.
created_at	STRING	no	The time when the volume was created.
created_by	STRING	no	The principal who created the volume.

Examples

SQL

-- Create and drop a volume, then list the dropped volumes in the current schema.
> USE CATALOG my_catalog;
> USE SCHEMA my_schema;
> CREATE VOLUME my_volume;
> DROP VOLUME my_volume;
> SHOW VOLUMES DROPPED;
  catalog    schema    volume_name volume_id                            owner         comment created_at                    created_by
  ---------- --------- ----------- ------------------------------------ ------------- ------- ----------------------------- -------------
  my_catalog my_schema my_volume   6ca7be55-8f58-47a7-85ee-7a59082fd17a alf@melmak.et         2023-05-03 AD at 18:17:00 UTC alf@melmak.et

-- List dropped volumes from a different schema, limiting the results.
> CREATE VOLUME my_catalog.other_schema.other_volume;
> DROP VOLUME my_catalog.other_schema.other_volume;
> SHOW VOLUMES DROPPED IN my_catalog.other_schema LIMIT 10;
  catalog    schema       volume_name  volume_id                            owner         comment created_at                    created_by
  ---------- ------------ ------------ ------------------------------------ ------------- ------- ----------------------------- -------------
  my_catalog other_schema other_volume b819f397-c51f-4e60-8acc-05d4d4a7e084 alf@melmak.et         2023-05-04 AD at 08:20:00 UTC alf@melmak.et

Related articles

• SHOW VOLUMES
• UNDROP VOLUME