SHOW VOLUMES DROPPED

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19.11 and above check marked yes 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.

Examples

-- 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
  • Last updated on