ALTER SHARE
The ALTER SHARE statement changes the include pattern on a Share you own, which controls which tables and views the Share exposes. See table-level security for the feature overview.
INCLUDE_PATTERN is the only option ALTER SHARE accepts. To publish new data to a Share, use UPDATE SHARE. To change a Share's access, visibility, or update mode, recreate it with CREATE OR REPLACE SHARE.
Only the owner of a Share can alter it. Altering a Share owned by another account fails as though the Share doesn't exist.
Syntax
ALTER SHARE [ IF EXISTS ] <share name> SET INCLUDE_PATTERN '<pattern list>';
ALTER SHARE [ IF EXISTS ] <share name> RESET INCLUDE_PATTERN;
Parameters
| Parameter | Description |
|---|---|
<share name> | The name of a Share you own. Without IF EXISTS, a Share that doesn't exist raises an error |
IF EXISTS | Makes the statement a no-op when the Share doesn't exist, for both SET and RESET |
<pattern list> | A comma-separated list of schema.table patterns. See the INCLUDE_PATTERN clause |
SET and RESET produce three distinct states:
| Statement | Stored value | Effect |
|---|---|---|
SET INCLUDE_PATTERN 'reporting.*' | [reporting.*] | The Share exposes what the patterns match |
SET INCLUDE_PATTERN '' | [] | The Share exposes nothing. Only the default schema remains |
RESET INCLUDE_PATTERN | NULL | The Share exposes the whole database |
Read the stored value back from the INCLUDE_PATTERN column of LIST SHARES or MD_INFORMATION_SCHEMA.OWNED_SHARES.
SET INCLUDE_PATTERN validates every pattern against the source database's live catalog, and the check is all-or-nothing: if one pattern in the list matches nothing, the statement fails and the stored pattern is left unchanged. See validation for the rules that decide whether a pattern matches.
Propagation
An edit reaches an already-attached consumer on the next update cycle, within a minute or two. Re-attaching the Share picks it up immediately. This includes your own held-open connection, since the owner gets no fast path. The Share URL doesn't change, so an edit never forces consumers to re-attach.
Example usage
-- Restrict an existing share to one schema.
ALTER SHARE sales_share SET INCLUDE_PATTERN 'reporting.*';
-- Expose several patterns; they are stored as a list, in order.
ALTER SHARE sales_share SET INCLUDE_PATTERN 'reporting.*, finance.salaries';
-- Expose nothing.
ALTER SHARE sales_share SET INCLUDE_PATTERN '';
-- Go back to exposing the whole database.
ALTER SHARE sales_share RESET INCLUDE_PATTERN;
-- No-op when the share is absent, rather than an error.
ALTER SHARE IF EXISTS maybe_missing RESET INCLUDE_PATTERN;
Troubleshooting
| Error | Cause |
|---|---|
does not exist | The Share doesn't exist, or it's owned by another account. Add IF EXISTS to make a missing Share a no-op |
syntax error | An option other than INCLUDE_PATTERN was given. ALTER SHARE accepts no other options |
For errors raised by the pattern itself, such as a pattern that matches nothing or names a missing schema, see INCLUDE_PATTERN errors.