Categories:: Data Loading / Unloading DDL

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>
                 }
            ]

Parameters¶

LIKE 'pattern'

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%' ...

[ 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 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 (e.g. 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 (i.e. the command returns the objects you have privileges to view in the database).
No database: ACCOUNT is the default (i.e. the command returns the objects you have privileges to view in your account).

Usage Notes¶

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:

| created_on | name | database_name | schema_name | url | has_credentials | has_encryption_key | owner | comment | region | type | cloud | notification_channel | storage_integration |

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.

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