SHOW ICEBERG TABLES¶

Lists the Iceberg tables for which you have access privileges.

The command can be used to list Iceberg tables for the current/specified database or schema, or across your entire account.

This command returns different output columns than SHOW TABLES. The output returns Iceberg table metadata and properties, ordered lexicographically by database, schema, and Iceberg table name (see Output in this topic for descriptions of the output columns). This is important to note if you want to filter the results using the provided filters.

Note that this topic refers to Iceberg tables as simply “tables” except where specifying Iceberg tables avoids confusion.

See also:: CREATE ICEBERG TABLE , DROP ICEBERG TABLE , DESCRIBE ICEBERG TABLE , ALTER ICEBERG TABLE , SHOW TABLES

Syntax¶

SHOW [ TERSE ] [ ICEBERG ] TABLES [ LIKE '<pattern>' ]
                                  [ IN
                                        {
                                          ACCOUNT                  |

                                          DATABASE                 |
                                          DATABASE <database_name> |

                                          SCHEMA                   |
                                          SCHEMA <schema_name>     |
                                          <schema_name>
                                        }
                                  ]
                                  [ STARTS WITH '<name_string>' ]
                                  [ LIMIT <rows> [ FROM '<name_string>' ] ]

Copy

Parameters¶

TERSE

Optionally returns only a subset of the output columns:

created_on
name
kind

The kind column value is always ICEBERG TABLE.
database_name
schema_name

Default: No value (all columns are included in the output)

ICEBERG

Returns Iceberg tables only.

LIKE 'pattern'

Optionally filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (% and _).

For example, the following patterns return the same results:

... LIKE '%testing%' ...

... LIKE '%TESTING%' ...

. Default: No value (no filtering is applied to the output).

[ IN ... ]

Optionally specifies the scope of the command. Specify one of the following:

ACCOUNT

Returns records for the entire account.

DATABASE, . DATABASE db_name

Returns records for the current database in use or for a specified database (db_name).

If you specify DATABASE without db_name and no database is in use, the keyword has no effect on the output.

SCHEMA, . SCHEMA schema_name, . schema_name

Returns records for the current schema in use or a specified schema (schema_name).

SCHEMA is optional if a database is in use or if you specify the fully qualified schema_name (for example, db.schema).

If no database is in use, specifying SCHEMA has no effect on the output.

Default: Depends on whether the session currently has a database in use:

Database: DATABASE is the default (that is, the command returns the objects you have privileges to view in the database).
No database: ACCOUNT is the default (that is, the command returns the objects you have privileges to view in your account).

STARTS WITH 'name_string'

Optionally filters the command output based on the characters that appear at the beginning of the object name. The string must be enclosed in single quotes and is case-sensitive.

For example, the following strings return different results:

... STARTS WITH 'B' ...

... STARTS WITH 'b' ...

. Default: No value (no filtering is applied to the output)

LIMIT rows [ FROM 'name_string' ]

Optionally limits the maximum number of rows returned, while also enabling “pagination” of the results. The actual number of rows returned might be less than the specified limit. For example, the number of existing objects is less than the specified limit.

The optional FROM 'name_string' subclause effectively serves as a “cursor” for the results. This enables fetching the specified number of rows following the first row whose object name matches the specified string:

The string must be enclosed in single quotes and is case-sensitive.
The string does not have to include the full object name; partial names are supported.

Default: No value (no limit is applied to the output)

Note

For SHOW commands that support both the FROM 'name_string' and STARTS WITH 'name_string' clauses, you can combine both of these clauses in the same statement. However, both conditions must be met or they cancel out each other and no results are returned.

In addition, objects are returned in lexicographic order by name, so FROM 'name_string' only returns rows with a higher lexicographic value than the rows returned by STARTS WITH 'name_string'.

For example:

... STARTS WITH 'A' LIMIT ... FROM 'B' would return no results.
... STARTS WITH 'B' LIMIT ... FROM 'A' would return no results.
... STARTS WITH 'A' LIMIT ... FROM 'AB' would return results (if any rows match the input strings).

Access control requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege	Object	Notes
SELECT	Iceberg table	To see a particular Iceberg table in the output for SHOW ICEBERG TABLES, a role must have the SELECT privilege on that table.

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

If an account (or database or schema) has a large number of Iceberg tables, then searching the entire account (or database or schema) can consume a significant amount of compute resources.

The command does not require a running warehouse to execute.
The value for LIMIT rows cannot exceed 10000. If LIMIT rows is omitted, the command results in an error if the result set is larger than 10K rows.

To view results for which more than 10K records exist, either include LIMIT rows or query the corresponding view in the Snowflake Information Schema.

To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.

Output¶

Note

The following output schema is for the SHOW ICEBERG TABLES command. For information about the output of SHOW TABLES, see Identifying Iceberg Tables with SHOW TABLES (in this topic).

The command output provides table properties and metadata in the following columns:

Column	Description
`created_on`	Date and time when the table was created.
`name`	Name of the table.
`database_name`	Database in which the table is stored.
`schema_name`	Schema in which the table is stored.
`owner`	Role that owns the table.
`external_volume_name`	Name of the external volume where the Iceberg table data and metadata are stored.
`catalog_name`	Name of the catalog integration object associated with the Iceberg table when the table is not managed by Snowflake. `SNOWFLAKE` when the table is managed by Snowflake.
`iceberg_table_type`	Type of Iceberg table. `UNMANAGED` if the table is not managed by Snowflake. `NOT ICEBERG` otherwise.
`catalog_table_name`	Name of the table as recognized by the catalog.
`catalog_namespace`	Catalog namespace for the table. The namespace defined when the table was created. Otherwise, the default namespace associated with the catalog integration used by the table.
`file_path`	Relative path from the `EXTERNAL_VOLUME` location to the table metadata and data files. Defined as `BASE_LOCATION` when you create certain types of Iceberg tables.
`can_write_metadata`	Signifies whether Snowflake can write metadata to the location specified by the `file_path`.
`comment`	Comment for the table.
`name_mapping`	List of objects with information about table columns that use column projection. For more information, see name_mapping.
`owner_role_type`	The type of role that owns the object, either `ROLE` or `DATABASE_ROLE`. . If a Snowflake Native App owns the object, the value is `APPLICATION`. . Snowflake returns NULL if you delete the object because a deleted object does not have an owner role.

name_mapping¶

The name_mapping output column provides information about table columns that use column projection.

If a table doesn’t contain any columns with an associated name mapping, the output column has a value of [NULL]. Otherwise, the value is a list of objects, where each object corresponds to a column that has an associated name mapping (sometimes referred to as a mapped field). Each object can contain the following three properties:

field-id: The Iceberg field ID.
names: A list of name strings for the field.
fields: A list of field mappings for the child fields of struct, map, or list columns.

For example:

[
  {
    "field-id": 1,
    "names": [
      "id",
      "record_id"
    ]
  },
  {
    "field-id": 2,
    "names": [
      "data"
    ]
  },
  {
    "field-id": 3,
    "names": [
      "location"
    ],
    "fields": [
      {
        "field-id": 4,
        "names": [
          "latitude",
          "lat"
        ]
      },
      {
        "field-id": 5,
        "names": [
          "longitude",
          "long"
        ]
      }
    ]
  }
]

Copy

Note

Field IDs can be non-consecutive if a column (or a field in a structured type column) doesn’t have an associated name mapping.

Examples¶

Show all the Iceberg tables whose name starts with glue that you have privileges to view in the tpch.public schema:

SHOW ICEBERG TABLES LIKE 'glue%' IN tpch.public;

Copy

Identifying Iceberg tables with SHOW TABLES¶

The SHOW TABLES command output has a column that indicates whether a table is an Iceberg table. This column appears in addition to the regular SHOW TABLES output columns.

The column has the following name and possible values:

Column name	Values
is_iceberg	`Y` if the table is an Iceberg table; `N` otherwise.