SHOW STAGES¶

Lists all the stages for which you have access privileges. This command can be used to list the stages for a specified schema or database (or the current schema/database for the session), or your entire account.

See also:: CREATE STAGE , ALTER STAGE , DROP STAGE , DESCRIBE STAGE

Syntax¶

SHOW STAGES [ LIKE '<pattern>' ]
            [ IN
                 {
                   ACCOUNT                                         |

                   DATABASE                                        |
                   DATABASE <database_name>                        |

                   SCHEMA                                          |
                   SCHEMA <schema_name>                            |
                   <schema_name>

                   APPLICATION <application_name>                  |
                   APPLICATION PACKAGE <application_package_name>  |
                 }
            ]

Copy

Parameters¶

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.

APPLICATION application_name, . APPLICATION PACKAGE application_package_name: Returns records for the named Snowflake Native App or application package.

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

Usage notes¶

Columns that start with the prefix is_ return either Y (yes) or N (no).
The command does not require a running warehouse to execute.
The command returns a maximum of 10K records for the specified object type, as dictated by the access privileges for the role used to execute the command; any records above the 10K limit are not returned, even with a filter applied.

To view results for which more than 10K records exist, query the corresponding view (if one exists) 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¶

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

Column	Description
created_on	Date and time when the stage was created.
name	Name of the stage.
database_name	Database in which the stage is stored.
schema_name	Schema in which the stage is stored.
url	URL for the external stage; blank for an internal stage.
has_credentials	Indicates that the external stage has access credentials; always `N` for an internal stage.
has_encryption_key	Indicates that the external stage contains encrypted files; always `N` for an internal stage.
owner	Role that owns the stage.
comment	Comment for the stage.
region	Region where the stage is located.
type	Indicates whether the stage is an external stage or internal stage, as well as whether the internal stage is permanent or temporary.
cloud	Cloud provider; always `NULL` for an internal stage.
notification_channel	Amazon Resource Name of the Amazon SQS queue for the stage. Deprecated column.
storage_integration	Storage integration associated with the stage; always `NULL` for an internal stage.
endpoint	The S3-compatible API endpoint associated with the stage; always `NULL` for stages that are not S3-compatible.
owner_role_type	The type of role that owns the object, for example `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.
directory_enabled	Indicates whether the stage has a directory table enabled. `Y` if a directory table is enabled, `N` if not enabled.

For more information about the stage properties, see CREATE STAGE.