# INCLUDE_PATTERN clause > Pattern syntax, matching rules, and validation for the INCLUDE_PATTERN clause of CREATE SHARE and ALTER SHARE. `INCLUDE_PATTERN` limits which tables and views a Share exposes. [`CREATE SHARE`](create-share.md) takes it as an option, and [`ALTER SHARE`](alter-share.md) sets or clears it on an existing Share. For a walkthrough of both, see [table-level security](/key-tasks/sharing-data/table-level-security/). ## Syntax ```sql INCLUDE_PATTERN '[, ...]' ``` A pattern list is a single string holding one or more comma-separated patterns. The Share exposes the union of everything the patterns match. ## The three include-pattern states | Include pattern | Stored value | Effect | | --- | --- | --- | | Not set | `NULL` | The Share exposes the whole database. This is the default, and the state `RESET INCLUDE_PATTERN` returns to | | `''` (empty string) | `[]` | The Share exposes nothing. Only the default schema remains, so `USE` and unqualified lookups still resolve | | A list of patterns | `[, ...]` | The Share exposes the union of everything the patterns match | Read the stored value back from the `INCLUDE_PATTERN` column of [`LIST SHARES`](list-shares.md) or [`MD_INFORMATION_SCHEMA.OWNED_SHARES`](md_information_schema/owned_shares.md). Blank entries between patterns are dropped, including a trailing comma, so `'reporting.*, , finance.salaries,'` stores as `[reporting.*, finance.salaries]`. A list that trims to nothing, such as `' , '`, is rejected rather than treated as "expose nothing". ## Pattern syntax A pattern matches a qualified `schema.table` name, and it matches views exactly as it matches tables. `*` is the only wildcard, and it matches within one segment, never across the dot. | Pattern | Matches | | --- | --- | | `reporting.*` | Every table and view in the `reporting` schema | | `main.orders` | The `orders` table in `main` | | `orders` | The `orders` table in `main`. A pattern with no dot is qualified with the default schema | | `*.orders` | Every table named `orders`, in any schema | | `reporting.fact_*` | Every table in `reporting` whose name starts with `fact_` | | `*` | Every table in `main` only. A bare `*` is one segment, so it means `main.*`, not every schema | | `*.*` | Every table in every schema | Matching rules: - **Matching is case-insensitive**, quoted or not, because DuckDB identifiers are case-insensitive. - **A pattern with no schema means `main` only.** To match a name across every schema, write `*.orders` rather than `orders`. - **A pattern has at most two segments.** An unterminated quote, a third segment, or text after a closing quote raises an error. - **`*` is the only wildcard.** `_`, `?`, `[`, `]`, and `\` are literal characters, so `weird.a?b` matches only the table named `a?b`. They need no quoting. - **Double-quote a segment to make `.`, `*`, and `,` literal**, so you can name tables and views whose names contain those characters. Write `""` for a literal double quote. Quoting changes which characters are literal, not how case is treated. - **Spaces inside a quoted segment are part of the name.** `reporting." q1 sales "` matches a table named ` q1 sales `, including the spaces. - **A quoted segment with no schema is still one segment**, qualified with `main`. `"hello.world"` means `main."hello.world"`, the table whose name contains a dot. Quoting applies per segment, so one segment can be quoted while the other isn't: | Pattern | Matches | | --- | --- | | `"hello.world"` | The table named `hello.world` in `main`, not the `world` table in a `hello` schema | | `reporting."hello.world"` | The table named `hello.world` in `reporting` | | `"reporting*ten"."hello.world"` | The table named `hello.world` in the schema named `reporting*ten`, where `*` is a literal character | ## Validation Both statements validate every pattern against the source database's live catalog as they run, which catches a typo before it produces an emptier Share than you intended. The check is all-or-nothing: if one pattern in the list matches nothing, the statement fails and the stored pattern is left unchanged. | Pattern | Requirement | | --- | --- | | `.*` with a literal schema name | The schema exists. A schema holding no tables is accepted | | `*.*` | Always valid, even against a database with no tables or schemas at all | | Any other pattern | At least one table or view matches. This includes a `.*` pattern whose schema segment holds a wildcard, so `stag*.*` is rejected unless it matches a real table, even when a schema named `staging` exists | | `''` | Skips validation, because an empty include-list has nothing to match | A pattern is validated when you set it and isn't re-evaluated afterward, so dropping a matched table later doesn't raise an error. On an `UPDATE AUTOMATIC` Share the table stops being exposed. On an `UPDATE MANUAL` Share, consumers keep seeing the frozen snapshot, including the dropped table, until you run [`UPDATE SHARE`](update-share.md). ## What a filtered Share exposes - Hidden tables and views are absent from the consumer's catalog, and a query naming one fails with a standard missing-object error. The "Did you mean...?" suggestion only recommends accessible tables and views. - A schema appears in the consumer's catalog when a pattern names the whole schema (`.*` or `*.*`), or when at least one of its tables is visible. The default schema is always present. - Foreign keys that reference a hidden table are stripped from the tables the Share does expose. - Macros, sequences, and types aren't matched by name. They ride their schema's visibility, so every macro, sequence, and type in the always-visible default schema is exposed, including when the pattern is `''`. - Object definitions aren't rewritten. A visible view's or macro's definition text can name a hidden table. The hidden table's data stays unreadable, but its name can appear. - The pattern is enforced on MotherDuck's servers, not in the client, so an older or modified client sees the same filtered catalog. ## Errors | Error | Cause | | --- | --- | | `matches no table or view` | A pattern matches no table or view in the source database. Check the schema qualifier: a pattern with no dot is qualified with the default schema, so `orders` means `main.orders` | | `schema that does not exist` | A `.*` pattern names a schema that isn't in the source database | | `must contain at least one pattern` | The value is non-empty but trims to nothing, such as `' , '` or `' '`. Write `''` when you mean "expose nothing" | | `INCLUDE_PATTERN is not supported for shares of DUCKLAKE databases` | The source database is a DuckLake or Iceberg database. See [limitations](/key-tasks/sharing-data/table-level-security/#limitations) | | `INCLUDE_PATTERN is too long` | The pattern list exceeds the 16,384-character maximum | | `Filtered shares (INCLUDE_PATTERN) are not enabled for this account` | Table-level security isn't enabled for your account. It requires a **Business** or **Enterprise** plan | ## Related - [Table-level security](/key-tasks/sharing-data/table-level-security/) - [CREATE SHARE](create-share.md) - [ALTER SHARE](alter-share.md) - [LIST SHARES](list-shares.md) --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fsql-reference%2Fmotherduck-sql-reference%2Finclude-pattern%2F&page_title=INCLUDE_PATTERN%20clause&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.