LIST SHARES
The LIST SHARES statement lists all shares created by the current user.
It provides the same information as querying the MD_INFORMATION_SCHEMA.OWNED_SHARES view. You can also use the table function md_list_database_shares() for a subset of this information.
tip
To see shares that have been shared with you (by others), query the MD_INFORMATION_SCHEMA.SHARED_WITH_ME view instead.
Syntax
-- Using DDL (lists all owned shares with details)
LIST SHARES;
-- Equivalent to querying the information schema view
SELECT * FROM MD_INFORMATION_SCHEMA.OWNED_SHARES;
-- Using table function (returns a subset of columns)
SELECT name, url, source_db_name FROM md_list_database_shares();
Output
The LIST SHARES statement and SELECT * FROM MD_INFORMATION_SCHEMA.OWNED_SHARES return a table with the following columns:
| Column Name | Data Type | Value |
|---|---|---|
| NAME | STRING | The name of the share |
| URL | STRING | The share_url which can be used to attach the share |
| SOURCE_DB_NAME | STRING | The name of the database where this share was created from |
| SOURCE_DB_UUID | UUID | UUID of the database where this share was created from |
| ACCESS | STRING | Whether anyone (UNRESTRICTED) within the same cloud region or only organization members (ORGANIZATION) can attach to the share by its share_url. RESTRICTED shares are hidden from the list. |
| VISIBILITY | STRING | Whether the share is DISCOVERABLE or HIDDEN |
| UPDATE | STRING | The share's update mode (MANUAL vs. AUTOMATIC) |
| CREATED_TS | TIMESTAMP | The share's creation time |
| GRANTS | STRUCT(grantee_name VARCHAR, "access" VARCHAR)[] | A list of all grants that are active for the share. A grantee is a user or a role |
| INCLUDE_PATTERN | VARCHAR[] | The share's include pattern. NULL when the share exposes the whole database, an empty list when it exposes nothing, otherwise the list of patterns |
note
Shares are region-scoped based on your Organization's cloud region. Each MotherDuck Organization is scoped to a single cloud region that must be chosen at Org creation when signing up.
MotherDuck is available on AWS in six regions:
- US East (N. Virginia):
us-east-1 - US West (Oregon):
us-west-2 - Europe (Frankfurt):
eu-central-1 - Europe (Dublin):
eu-west-1 - Asia Pacific (Tokyo):
ap-northeast-1 - Asia Pacific (Sydney):
ap-southeast-2