INCLUDE_PATTERN clause

INCLUDE_PATTERN limits which tables and views a Share exposes. CREATE SHARE takes it as an option, and ALTER SHARE sets or clears it on an existing Share. For a walkthrough of both, see table-level security.

Syntax

INCLUDE_PATTERN '<pattern>[, <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	[<pattern>, ...]	The Share exposes the union of everything the patterns match

Read the stored value back from the INCLUDE_PATTERN column of LIST SHARES or MD_INFORMATION_SCHEMA.OWNED_SHARES.

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
<schema>.* 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.

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 (<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 <schema>.* 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
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
• CREATE SHARE
• ALTER SHARE
• LIST SHARES