Categories:

Data Loading / Unloading DDL

SHOW PIPES

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

See also:

ALTER PIPE , CREATE PIPE , DESCRIBE PIPE , DROP PIPE

Syntax

SHOW PIPES [ LIKE '<pattern>' ]
           [ IN { ACCOUNT | [ DATABASE ] <db_name> | [ SCHEMA ] <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 ACCOUNT | [ DATABASE ] db_name | [ SCHEMA ] schema_name

Specifies the scope of the command, which determines whether the command lists records only for the current/specified database or schema, or across your entire account:

The DATABASE or SCHEMA keyword is not required; you can set the scope by specifying only the database or schema name. Likewise, the database or schema name is not required if the session currently has a database in use:

  • If DATABASE or SCHEMA is specified without a name and the session does not currently have a database in use, the parameter has no effect on the output.

  • If SCHEMA is specified with a name and the session does not currently have a database in use, the schema name must be fully qualified with the database name (e.g. testdb.testschema).

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

DB in use

DATABASE is the default (i.e. the command returns the objects you have privileges to view in the database).

DB not in use

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 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 provide stage properties and metadata in the following columns:

| created_on | name | database_name | schema_name |definition | owner | notification_channel | comment |

Column

Description

created_on

Date and time when the pipe was created.

name

Name of the pipe.

database_name

Database that the pipe belongs to.

schema_name

Schema that the pipe belongs to.

definition

COPY statement used to load data from queued files into a Snowflake table.

owner

Name of the role that owns the pipe (i.e. that has the OWNERSHIP privilege on the pipe).

notification_channel

Amazon Resource Name of the Amazon SQS queue for the stage named in the DEFINITION column.

comment

Comment for this pipe.

integration

Name of the notification integration used for Snowpipe (automatic loading of data files using event notifications) or external tables (automatic refreshing of external table metadata).

Examples

Show all the pipes that you have privileges to view in the public schema in the mydb database:

use database mydb;

show pipes;