Categories:

Database, Schema, & Share DDL

SHOW SHARES

Lists all shares available in the system:

  • Outbound shares (to consumers) that have been created in your account (as a provider).

  • Inbound shares (from providers) that are available for your account to consume.

See also:

CREATE SHARE , DESCRIBE SHARE , DROP SHARE

Syntax

SHOW SHARES [ LIKE '<pattern>' ]

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

Usage Notes

  • Executing this command using any role other than ACCOUNTADMIN returns empty results.

  • 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 kind column displays:

    • INBOUND indicates the share is available to your account to consume (i.e. you can create a database from the share).

    • OUTBOUND indicates that your account is sharing data with other accounts and this share was created in your account.

  • For OUTBOUND shares, if accounts have been added to the share, the to column displays these accounts. The maximum number of accounts displayed in this column is three; however, there is no hard limit on the number of accounts that can be added to a share.

Examples

Show all shares that have been created in your account or are available to consume by your account:

SHOW SHARES;

+-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------+
| created_on                    | kind     | name                    | database_name         | to               | owner        | comment                                |
|-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------|
| 2016-07-09 19:18:09.821 -0700 | INBOUND  | SFC_SAMPLES.SAMPLE_DATA | SNOWFLAKE_SAMPLE_DATA |                  |              | Sample data sets provided by Snowflake |
| 2017-06-15 17:02:29.625 -0700 | OUTBOUND | AB67890.SALES_S         | SALES_DB              | XY12345, YZ23456 | ACCOUNTADMIN |                                        |
+-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------+