Categories:

Database, Schema, & Share DDL

SHOW SHARES

Provider sharing not enabled for all accounts

Provider sharing is enabled by default for most, but not all accounts.

If you encounter errors when attempting to share data with consumers, the feature may not be enabled for your account. To inquire about enabling it, please contact Snowflake Support.

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 , ALTER SHARE , DROP SHARE , DESCRIBE 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

• The command lists shares only for users with a role that has the IMPORT SHARE privilege:

  • By default, the ACCOUNTADMIN role has this privilege.

  • A user with the ACCOUNTADMIN role can delegate this privilege. See Enabling non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks.

  • If a user without the IMPORT SHARE privilege runs this command, no shares are listed.

  Note

  Executing this command without sufficient privileges 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 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 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 |                                        |
+-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------+