# MotherDuck Documentation - MD_INFORMATION_SCHEMA

> Introduction to MD_INFORMATION_SCHEMA

Generated: 2026-08-25

> MotherDuck is a serverless cloud data warehouse built on DuckDB.
It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics.

## Key capabilities

- **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses)
- **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session
- **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language
- **Data Sharing**: Share databases and query results with team members and external users
- **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI
- **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more
- **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation

## When to use MotherDuck

Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP.

## Agent guidance

If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation.

For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows.

To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question.

For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json.

## Account setup for agents

If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request.

`POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`.

Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership.

Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup.

## Included documentation

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/database_snapshots

# DATABASE_SNAPSHOTS view
> Query the DATABASE_SNAPSHOTS view for snapshot history and data recovery options.
The `MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS` view provides information about database snapshots for databases visible to the current user. This can be used for data recovery or to inspect snapshot history.

## Schema

When you query the `MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS` view, the results contain at least one row per database visible to the user. MotherDuck treats the most recent snapshot for each database as the "active" snapshot.

In addition, if you have historical snapshots or named snapshots associated with any of your databases, they will show up here too.

The `MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS` view has the following schema:

| Column Name | Data Type | Value                             |
|-------------|-----------|-----------------------------------|
| DATABASE_NAME | VARCHAR    | The name of the database |
| DATABASE_ID | UUID    | Unique ID for the database |
| SNAPSHOT_ID | UUID | Unique ID for the snapshot |
| SNAPSHOT_NAME | VARCHAR | Optional name for the snapshot (NULL for automatic snapshots) |
| CREATED_TS | TIMESTAMP | Time when the snapshot was created |
| ACTIVE_BYTES | UBIGINT | The logical size of the database at this snapshot |
| BYTES_WRITTEN | UBIGINT | The number of bytes written since the previous snapshot |
| BYTES_DELETED | UBIGINT | The number of bytes deleted since the previous snapshot |
| USER_NAME | VARCHAR | The user who owns the database corresponding to the snapshot (user or service account) |
| USER_ID | UUID | Unique ID of the database owner |

## Example usage

```sql
FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS
LIMIT 10;
```

Get the active (most recent) snapshot for each database:

```sql
SELECT *
FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY database_id
  ORDER BY created_ts DESC
) = 1
ORDER BY database_name;
```

List named snapshots for a specific database:

```sql
SELECT *
FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS
WHERE database_name = 'example_db'
  AND snapshot_name IS NOT NULL
ORDER BY created_ts DESC;
```

### View snapshots with retention settings

Combine `DATABASE_SNAPSHOTS` with [`DATABASES`](/sql-reference/motherduck-sql-reference/md_information_schema/databases) to see all snapshots for a database along with its retention period:

```sql
SELECT
    s.database_name,
    d.historical_snapshot_retention,
    s.snapshot_id,
    s.snapshot_name,
    s.created_ts,
    s.active_bytes
FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS s
JOIN MD_INFORMATION_SCHEMA.DATABASES d
    ON s.database_id = d.uuid
WHERE s.database_name = 'example_db'
ORDER BY s.created_ts DESC;
```

This query helps you understand which snapshots are available for [point-in-time restore](/concepts/data-recovery) and how long automatic snapshots will be retained before garbage collection.

### View all named snapshots in your organization

**Requires permission:** View organization-wide storage information — included by default in the Admin preset role. A custom role qualifies when it inherits a role that includes the permission. Applies to viewing snapshots across the organization; your own snapshots need no permission. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do).

With that permission, combine [`STORAGE_INFO`](/sql-reference/motherduck-sql-reference/md_information_schema/storage_info) with `DATABASE_SNAPSHOTS`:

```sql
SELECT
    s.user_name,
    s.database_name,
    s.snapshot_name,
    s.snapshot_id,
    s.created_ts,
    s.active_bytes
FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS s
WHERE s.snapshot_name IS NOT NULL
ORDER BY s.user_name, s.database_name, s.created_ts DESC;
```

::::note Snapshot naming conflicts
Snapshot names must be unique per user, but different users can have snapshots with the same name. When restoring or referencing snapshots programmatically, use `user_id` or `user_name` along with `snapshot_name` to avoid ambiguity.
::::

## Understanding timestamps

Use `DATABASE_SNAPSHOTS.created_ts` for **snapshot creation times** — this is what you need for [point-in-time restore](/concepts/data-recovery) operations with [`ALTER DATABASE SET SNAPSHOT`](/sql-reference/motherduck-sql-reference/alter-database-snapshot).

Use [`STORAGE_INFO`](/sql-reference/motherduck-sql-reference/md_information_schema/storage_info)`.created_ts` for **database creation times** — this is useful for billing and lifecycle management.

| View | `created_ts` meaning | Use case |
|------|---------------------|----------|
| `DATABASE_SNAPSHOTS` | When the snapshot was created | Point-in-time restore, finding snapshots to recover |
| `STORAGE_INFO` | When the database was created | Storage billing, database lifecycle management |

## Troubleshooting

### Why do I only see one snapshot for my database?

If your database's retention is set to zero days, you will only be able to see the "active" snapshot associated with your database. To modify your database's retention see [`ALTER DATABASE`](/sql-reference/motherduck-sql-reference/alter-database).

### Why can't I find a snapshot from a specific time?

Automatic snapshots are only retained for the duration specified by `snapshot_retention_days` and when data is actively modified. Once a snapshot falls outside this window, it is garbage-collected and cannot be recovered. To check your database's retention setting:

```sql
SELECT name, historical_snapshot_retention
FROM MD_INFORMATION_SCHEMA.DATABASES
WHERE name = 'example_db';
```

If you want to keep snapshots when only querying a database, you can either use a named snapshot or actively create a snapshot with:

```sql
CREATE SNAPSHOT OF example_db;
```

### How can I see the current retention period for my database

You can find the historical retention period in the information schema:

```sql
select name, historical_snapshot_retention from md_information_schema.databases
```

## See also

- [`DATABASES` view](/sql-reference/motherduck-sql-reference/md_information_schema/databases) — Database metadata including retention settings
- [`STORAGE_INFO` views](/sql-reference/motherduck-sql-reference/md_information_schema/storage_info) — Storage billing and lifecycle information
- [Data Recovery](/concepts/data-recovery) — Step-by-step guide to restoring databases from snapshots

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/databases

# DATABASES view


> Query the DATABASES view to list all databases you have created in MotherDuck.

The `MD_INFORMATION_SCHEMA.DATABASES` view provides information about the current databases that the user created.

## Schema

When you query the `MD_INFORMATION_SCHEMA.DATABASES` view, the query results contain one row for each database that the current user created.

The `MD_INFORMATION_SCHEMA.DATABASES` view has the following schema:

| Column Name | Data Type | Value                             |
|-------------|-----------|-----------------------------------|
| NAME        | STRING    | The name or alias of the database |
| UUID        | STRING    | The UUID of the database          |
| CREATED_TS  | TIMESTAMP | The database’s creation time |
| TRANSIENT   | BOOLEAN   | Whether the database is transient |
| HISTORICAL_SNAPSHOT_RETENTION | INTERVAL | Period of time the database's snapshots are retained after becoming inactive |
| TYPE        | STRING    | Database type (ducklake or default) |

## Example usage

```sql
from MD_INFORMATION_SCHEMA.DATABASES;
```

|         name         |                 uuid                 |       created_ts       | transient | historical_snapshot_retention |   type   |
|----------------------|--------------------------------------|------------------------|-----------|-------------------------------|----------|
| tpch_sf1000_template | 2c80b37d-d307-44d8-aff6-33ea2294bd35 | 2024-10-21 14:26:30-04 |   false   |          00:00:00             | default  |
| db1                  | 445864c7-5758-42a2-9a5c-2f16620ebc9f | 2024-09-15 09:32:05-04 |   false   |          1 day                | default  |
| foo                  | 4d829a9e-e0da-408c-aafa-0fc50186a588 | 2024-09-03 13:32:10-04 |   false   |          1 day                | default  |
| tpch_sf1000          | fc4bf9f4-80d1-4fd9-b6fe-d6d71f40ef42 | 2024-10-21 14:26:30-04 |   false   |          1 day                | default  |
| my_ducklake          | a1b2c3d4-e5f6-7890-abcd-ef1234567890 | 2025-03-10 11:15:42-04 |   false   |          NULL                 | ducklake |
| lake_with_retention  | b2c3d4e5-f6a7-8901-bcde-f12345678901 | 2025-04-01 09:20:18-04 |   false   |          7 days               | ducklake |

For DuckLake databases, `historical_snapshot_retention` shows `NULL` when snapshot retention is set to infinite (the default), or the configured `SNAPSHOT_RETENTION_DAYS` value.

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/query_history

# QUERY_HISTORY view


> Access detailed query history and metrics using the QUERY_HISTORY view (Business plan).

**Requires permission:** View query history — included by default in the Admin and Builder preset roles. A custom role qualifies when it inherits a role that includes the permission. Available on the Business plan. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do).

:::tip
The **Duckling overview** page (**Settings** → **Duckling overview**) gives a 24-hour summary of activity across every Duckling in the organization and requires the separate View Duckling overview permission. See [Duckling overview](/getting-started/interfaces/motherduck-quick-tour/#duckling-overview) for details.

You can monitor and cancel running queries in the MotherDuck UI under **Settings** → **Running Queries**. See [Running Queries](/getting-started/interfaces/motherduck-quick-tour/#running-queries) for details.
:::

The `MD_INFORMATION_SCHEMA.QUERY_HISTORY` view provides a consolidated view of all queries run across an organization.

:::note[Retention]
Query history is retained indefinitely. This retention policy may change in the future. If you depend on long-term query history, we recommend periodically exporting it to your own storage.
:::

## Schema

When you query the `MD_INFORMATION_SCHEMA.QUERY_HISTORY` view, the query results contain one row for each query that was run in the organization. Note that the information in this view will have some delays. A more realtime view of ongoing and completed queries that have not been captured in `QUERY_HISTORY` yet is provided through the [`MD_INFORMATION_SCHEMA.RECENT_QUERIES`](recent_queries.md) view.

The `MD_INFORMATION_SCHEMA.QUERY_HISTORY` view has the following schema:

| Column Name           | Data Type   | Value                             |
|-----------------------|-------------|-----------------------------------|
| QUERY_ID              | UUID        | A unique ID representing the particular query run. You can retrieve this value on the client with [`MD_LAST_QUERY_ID()`](../connection-management/last-query-id.md). |
| QUERY_TEXT            | STRING      | Query SQL text (up to 100k chars) |
| START_TIME            | TIMESTAMPTZ | Start time of the query |
| END_TIME              | TIMESTAMPTZ | End time of the query |
| EXECUTION_TIME        | INTERVAL    | Duration where the query is actively executing |
| WAIT_TIME             | INTERVAL    | Duration where the query is waiting on resources to become available. For example, a query may need to wait because other queries are using all available execution threads. Alternatively, a query may be waiting on data to become available (in case of data upload). Wait time is a useful signal for query optimization or uncovering workloads that could benefit from scaling compute **horizontally** with more ducklings or **vertically** with larger ducklings. |
| TOTAL_ELAPSED_TIME    | INTERVAL    | Total duration of the query |
| ERROR_MESSAGE         | STRING      | Error message, if the query returned an error |
| ERROR_TYPE            | STRING      | Error type, if the query returned an error |
| USER_AGENT            | STRING      | User agent of the client |
| USER_NAME             | STRING      | Identifier for the MotherDuck user in their organization |
| QUERY_NR              | UBIGINT     | ID of the query within the transaction that ran the query. Number that just increments for each query that is run within a given transaction |
| TRANSACTION_NR        | UBIGINT     | ID of the transaction that contained the query. Number that just increments for each new transaction on a given connection |
| CONNECTION_ID         | UUID        | Unique ID for the [client DuckDB connection](../connection-management/connection-duckdb-id.md) where the query was issued |
| DUCKDB_ID             | UUID        | Unique ID for the [client DuckDB instance](../connection-management/connection-duckdb-id.md) where the query was issued |
| DUCKDB_VERSION        | STRING      | Client DuckDB version that issued the query |
| INSTANCE_TYPE         | STRING      | The size of Duckling that the query was run on (Pulse / Standard / Jumbo / Mega / Giga / ...) |
| QUERY_TYPE            | STRING      | The nature of the query (DDL / DML / QUERY / ...) |
| BYTES_UPLOADED        | UBIGINT     | Number of bytes uploaded from client to server (relevant for hybrid queries) |
| BYTES_DOWNLOADED      | UBIGINT     | Number of bytes downloaded from server to client (relevant for hybrid queries) |
| BYTES_SPILLED_TO_DISK | UBIGINT     | Total number of bytes [spilled to disk](https://duckdb.org/docs/stable/guides/performance/how_to_tune_workloads.html#spilling-to-disk) for "larger than in-memory" workloads |
| DUCKLING_ID           | STRING      | Identifies the duckling that ran the query. It is composed of the user name and a qualifier (`rw` for read-write ducklings, or `rs.0`, `rs.1`, ... for the respective read scaling duckling) |
| SESSION_NAME          | STRING      | The [`session_name`](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/#session-names) that was supplied when connecting to MotherDuck |

The fields `START_TIME`, `END_TIME`, `TOTAL_ELAPSED_TIME`, `ERROR_MESSAGE`, and `ERROR_TYPE` are captured on the server (that is, when query starts and ends on server). In the future they will be based on client information too (taking better into account the full hybrid context).

## Example usage

```sql
from MD_INFORMATION_SCHEMA.QUERY_HISTORY limit 10;
```

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/recent_queries

# RECENT_QUERIES view


> View recent query activity across your organization using the RECENT_QUERIES view (Business plan).

**Requires permission:** View query history — included by default in the Admin and Builder preset roles. A custom role qualifies when it inherits a role that includes the permission. Available on the Business plan. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do).

:::tip
You can also monitor and cancel running queries in the MotherDuck UI under **Settings** → **Running Queries**. See [Running Queries](/getting-started/interfaces/motherduck-quick-tour/#running-queries) for details.

To monitor or interrupt individual connections, see [`md_active_server_connections()`](/sql-reference/motherduck-sql-reference/connection-management/monitor-connections/) and [`md_interrupt_server_connection()`](/sql-reference/motherduck-sql-reference/connection-management/interrupt-connections/).
:::

The `MD_INFORMATION_SCHEMA.RECENT_QUERIES` view provides a consolidated view of all running or completed queries across an organization. It complements the [`MD_INFORMATION_SCHEMA.QUERY_HISTORY`](query_history.md) view, which is geared towards analytics of past events and has some delays, with a more realtime view of active and completed queries that are not yet exposed in `QUERY_HISTORY`.

## Schema

When you query the `MD_INFORMATION_SCHEMA.RECENT_QUERIES` view, the query results contain one row for each query that is running or has completed in the organization. The information in this view is updated every couple of seconds.

The `MD_INFORMATION_SCHEMA.RECENT_QUERIES` view shares the same schema as the [`MD_INFORMATION_SCHEMA.QUERY_HISTORY`](query_history.md) view. The main difference is that for queries that have not completed yet the `END_TIME` field is null, and all other fields represent ongoing metrics that will be updated every few seconds.

Full schema:

| Column Name           | Data Type   | Value                             |
|-----------------------|-------------|-----------------------------------|
| QUERY_ID              | UUID        | A unique ID representing the particular query run. You can retrieve this value on the client with [`MD_LAST_QUERY_ID()`](../connection-management/last-query-id.md). |
| QUERY_TEXT            | STRING      | Query SQL text (up to 100k chars) |
| START_TIME            | TIMESTAMPTZ | Start time of the query |
| END_TIME              | TIMESTAMPTZ | End time of the query, if the query is completed |
| EXECUTION_TIME        | INTERVAL    | Duration where the query is actively executing |
| WAIT_TIME             | INTERVAL    | Duration where the query is waiting on resources to become available. For example a query needs to wait because other queries are using all available execution threads, or a query might be waiting on data to become available (in case of data upload). |
| TOTAL_ELAPSED_TIME    | INTERVAL    | Total duration of the query (the sum of execution time and wait time) |
| ERROR_MESSAGE         | STRING      | Error message, if the query returned an error |
| ERROR_TYPE            | STRING      | Error type, if the query returned an error |
| USER_AGENT            | STRING      | User agent of the client |
| USER_NAME             | STRING      | Identifier for the MotherDuck user in their organization |
| QUERY_NR              | UBIGINT     | ID of the query within the transaction that ran the query. Number that just increments for each query that is run within a given transaction |
| TRANSACTION_NR        | UBIGINT     | ID of the transaction that contained the query. Number that just increments for each new transaction on a given connection |
| CONNECTION_ID         | UUID        | Unique ID for the [client DuckDB connection](../connection-management/connection-duckdb-id.md) where the query was issued |
| DUCKDB_ID             | UUID        | Unique ID for the [client DuckDB instance](../connection-management/connection-duckdb-id.md) where the query was issued |
| DUCKDB_VERSION        | STRING      | Client DuckDB version that issued the query |
| INSTANCE_TYPE         | STRING      | The type of duckling that the query was run on (Pulse / Standard / Jumbo / Mega / Giga / ...) |
| QUERY_TYPE            | STRING      | The nature of the query (DDL / DML / QUERY / ...) |
| BYTES_UPLOADED        | UBIGINT     | Number of bytes uploaded from client to server (relevant for hybrid queries) |
| BYTES_DOWNLOADED      | UBIGINT     | Number of bytes downloaded from server to client (relevant for hybrid queries) |
| BYTES_SPILLED_TO_DISK | UBIGINT     | Total number of bytes [spilled to disk](https://duckdb.org/docs/stable/guides/performance/how_to_tune_workloads.html#spilling-to-disk) for "larger than in-memory" workloads |
| DUCKLING_ID           | STRING      | Identifies the duckling that ran the query. It is composed of the user name and a qualifier (`rw` for read-write ducklings, or `rs.0`, `rs.1`, ... for the respective read scaling duckling) |
| SESSION_NAME          | STRING      | The [`session_name`](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/#session-names) that was supplied when connecting to MotherDuck |

The fields `START_TIME`, `END_TIME`, `TOTAL_ELAPSED_TIME`, `ERROR_MESSAGE`, and `ERROR_TYPE` are captured on the server (that is, when query starts and ends on server). In the future they will be based on client information too (taking better into account the full hybrid context).

## Example usage

```sql
from MD_INFORMATION_SCHEMA.RECENT_QUERIES where end_time is null limit 10;
```

## Limitations

The `RECENT_QUERIES` view has been optimized for quickly answering questions such as "Which ongoing queries in my organization are taking a long time to complete". Query results of this view are therefore limited to 1000 rows, but support filter pushdowns so that this limit only applies after some basic filters. Take for example the following query:

```sql
from MD_INFORMATION_SCHEMA.RECENT_QUERIES where end_time is null and total_elapsed_time > '5 seconds';
```

The 1000 row limit only applies after the `end_time` and the `total_elapsed_time` filters have been applied, showing at most 1000 queries that are still ongoing and that are taking longer than 5 seconds. To check what filters are pushed down and apply before the row limit, the "filters" section of the MD_SERVER_RECENT_QUERIES table scan operator can be checked in the [query plan explain output](https://duckdb.org/docs/stable/guides/meta/explain).

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/storage_info

# STORAGE_INFO views


> View storage footprint, billing, and lifecycle information for all databases in your MotherDuck organization

**Requires permission:** View organization-wide storage information — included by default in the Admin preset role. A custom role qualifies when it inherits a role that includes the permission. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do).

## Overview

MotherDuck provides two views to look at how much storage is used - a current snapshot (`STORAGE_INFO`) and the previous 30 days of history (`STORAGE_INFO_HISTORY`).

The `MD_INFORMATION_SCHEMA.STORAGE_INFO` view provides comprehensive storage information for all databases in your MotherDuck organization. This view is essential for understanding storage usage, billing calculations, and database lifecycle management.

The `MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY` view provides storage information for up to the past 30 days of usage.

With permission to view organization-wide storage information, you can view your organization's storage breakdown on the [databases page](https://app.motherduck.com/settings/databases). The page shows the total current bytes across all databases and a breakdown for each database. You can also click a row to get a lifecycle breakdown for that database.

## Syntax

To see the latest snapshot:

```sql
SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO;
```

To see the history:

```sql
SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY;
```

## Columns

The `MD_INFORMATION_SCHEMA.STORAGE_INFO` view returns one row for each database in your organization with the following columns:

| Column Name             | Data Type | Description                                                                                                                            |
| ----------------------- | --------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| `database_name`         | VARCHAR   | Name of the database                                                                                                                   |
| `database_id`           | UUID      | Unique ID for the database                                                                                                             |
| `created_ts`            | TIMESTAMP | Time when the database was created                                                                                                     |
| `deleted_ts`            | TIMESTAMP | Time when the database was deleted (NULL if not deleted)                                                                               |
| `user_name`              | VARCHAR   | Username of the database owner                                                                                                         |
| `active_bytes`          | BIGINT    | Actively referenced bytes of the database                                                                                              |
| `historical_bytes`      | BIGINT    | Non-active bytes that are referenced by a share of this database                                                                       |
| `retained_for_clone_bytes` | BIGINT    | Bytes referenced by other databases (through zero-copy clone) that are no longer referenced by this database as active or historical bytes |
| `failsafe_bytes`        | BIGINT    | Bytes that are no longer referenced by any database or share                                                                           |
| `transient`             | BOOLEAN   | Whether the database is transient                                                                                                      |
| `historical_snapshot_retention` | INTERVAL | Period of time the database's snapshots are retained after becoming inactive                                                    |
| `computed_ts`           | TIMESTAMP | Time at which active_bytes, historical_bytes, etc. were computed                                                                       |

The `MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY` view has the same schema, but will return results from up to the past 30 days, so a single database might have multiple entries reflecting its state at different points in time.

## Examples

### Basic usage

View storage information for all databases in your organization:

```sql
-- Get storage information for all databases
SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO;
```

**Sample results:**

| database_name | database_id                          | created_ts          | deleted_ts | user_name | active_bytes | historical_bytes | retained_for_clone_bytes | failsafe_bytes | transient | historical_snapshot_retention | computed_ts             |
| ------------- | ------------------------------------ | ------------------- | ---------- | -------- | ------------ | ---------------- | --------------------- | -------------- | -------- | --------------------- | ---------------------- |
| test_db_1     | 7ed1baf3-e4ff-42c9-a37b-9f683905ce45 | 2024-12-02 20:18:36 | NULL       | bob      | 82063360     | 0                | 268496896             | 0              | false | 1 day | 2025-06-25 16:46:16.37 |
| test_db_2     | fcc16e53-d761-4e40-84ec-15570fab363e | 2024-11-12 03:38:52 | NULL       | jim      | 274432       | 0                | 0                     | 0              | false | 1 day | 2025-06-25 16:46:16.37 |

### Filtering and analysis

Find databases with high storage usage:

```sql
-- Find databases using more than 1GB of active storage
SELECT
    database_name,
    user_name,
    active_bytes,
    ROUND(active_bytes / 1000.0 / 1000.0 / 1000.0, 2) as active_gb
FROM MD_INFORMATION_SCHEMA.STORAGE_INFO
WHERE active_bytes > 1000000000  -- 1GB in bytes
ORDER BY active_bytes DESC;
```

### Storage cost analysis

Analyze storage costs by user:

```sql
-- Calculate total storage usage per user
SELECT
    user_name,
    COUNT(*) as database_count,
    SUM(active_bytes) as total_active_bytes,
    SUM(historical_bytes) as total_historical_bytes,
    SUM(retained_for_clone_bytes) as total_cloned_bytes,
    SUM(failsafe_bytes) as total_failsafe_bytes
FROM MD_INFORMATION_SCHEMA.STORAGE_INFO
GROUP BY user_name
ORDER BY total_active_bytes DESC;
```

Analyze active and failsafe storage footprint over the past week for a specific database:

```sql
SELECT active_bytes, failsafe_bytes, computed_ts
FROM MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY
WHERE database_name = "my_database"
AND computed_ts >= NOW - INTERVAL 7 DAYS
ORDER BY computed_ts DESC;
```

## Notes

- **Data Refresh**: Information in this view refreshes every 1-6 hours
- **Retention**: STORAGE_INFO_HISTORY only returns one set of results per day, even though the latest results are re-computed multiple times per day
- **Billing Data**: This view returns the underlying data used to power MotherDuck storage billing
- **Permissions**: You must have appropriate permissions to access this view
- **Organization Scope**: Only shows databases within your current organization

### Understanding timestamps

The `created_ts` column in `STORAGE_INFO` represents when the **database** was created. This is useful for understanding database age and lifecycle.

For [point-in-time restore](/concepts/data-recovery) scenarios, use [`DATABASE_SNAPSHOTS`](/sql-reference/motherduck-sql-reference/md_information_schema/database_snapshots) instead, where `created_ts` represents when each **snapshot** was created.

| View | `created_ts` meaning | Use case |
|------|---------------------|----------|
| `STORAGE_INFO` | When the database was created | Storage billing, database lifecycle management |
| `DATABASE_SNAPSHOTS` | When the snapshot was created | Point-in-time restore, finding snapshots to recover |

## Troubleshooting

### Common issues

**Outdated information**

- Data refreshes only happen periodically, so recent changes may not be immediately visible

**Permission denied errors**

- Ask someone with permission to assign roles to assign you a role that includes permission to view organization-wide storage information. The Admin preset role includes both permissions by default.
- Verify your authentication token is valid and has the required scope

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/owned_shares

# OWNED_SHARES view


> Query the OWNED_SHARES view to see database shares you have created.

The `MD_INFORMATION_SCHEMA.OWNED_SHARES` view provides information about shares created by the current user.

:::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`
:::

## Schema

Querying the `MD_INFORMATION_SCHEMA.OWNED_SHARES` view will return query results that contain one row for each share created by the current user.

The `MD_INFORMATION_SCHEMA.OWNED_SHARES` view has the following schema:

| 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 (referred to as UNRESTRICTED) or only organization members (referred to as ORGANIZATION) can attach to the share by its share_url |
| 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](/key-tasks/sharing-data/table-level-security/). NULL when the share exposes the whole database, an empty list when it exposes nothing, otherwise the list of patterns |

## Example usage

```sql
from MD_INFORMATION_SCHEMA.OWNED_SHARES;

select name, url, created_ts from MD_INFORMATION_SCHEMA.OWNED_SHARES;
```

|   name   |                           url                           | source_db_name |
|----------|---------------------------------------------------------|----------------|
| my_share | md:_share/my_share/2ef6b580-2445-4f4f-bce8-c13a85812464 | db1            |

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/shared_with_me

# SHARED_WITH_ME view


> Query the SHARED_WITH_ME view to see database shares available to you.

The `MD_INFORMATION_SCHEMA.SHARED_WITH_ME` view provides information about all shares that the current user can attach to (excluding their own created shares).

:::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`
:::

## Schema

When you query the `MD_INFORMATION_SCHEMA.SHARED_WITH_ME` view, the query results contain one row for each share that the current user can discover.

The `MD_INFORMATION_SCHEMA.SHARED_WITH_ME` view has the following schema:

| 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          |
| CREATED_TS  | TIMESTAMP | The share’s creation time |
| UPDATE  | STRING | The share’s update mode (MANUAL vs. AUTOMATIC) |
| ACCESS  | STRING | Whether anyone (referred to as UNRESTRICTED) or only organization members (referred to as ORGANIZATION) can attach to the share by its share_url |

## Example usage

```sql
from MD_INFORMATION_SCHEMA.SHARED_WITH_ME;
```

|            name             |                                    url                                     |       created_ts       |update    |  access   |
|-----------------------------|----------------------------------------------------------------------------|------------------------|----------|-----------|
| efs_ia_benchmark            | md:_share/efs_ia_benchmark/11597119-359a-4e02-8e5c-bc2b9b8c1908            | 2024-07-16 15:09:11-04 | MANUAL   | ORGANIZATION    |
| hf_load_test_share          | md:_share/hf_load_test_share/f76062a5-f1f5-4024-987d-fc2eea48311b          | 2024-07-29 09:07:33-04 | MANUAL   | ORGANIZATION    |
| mdw                         | md:_share/mdw/87be4635-fbfd-4d4b-9cae-b629842733d5                         | 2024-10-16 17:04:42-04 | AUTOMATIC | ORGANIZATION |
| my_sample_share             | md:_share/my_sample_share/c4ee2a30-2fb6-4cb5-b664-9030ae43ffdc             | 2024-09-24 17:53:23-04 | MANUAL   | ORGANIZATION    |

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/database_size

# Database Size


> Get storage size information for MotherDuck databases using PRAGMA database_size.

Database size can be fetched with `PRAGMA database_size;`. It contains attributes to allow insights into the sizes of MotherDuck databases.

## Alternative invocations

This Pragma can also be invoked as a tabular function with:
- `FROM pragma_database_size();`

## Schema

`PRAGMA database_size` has the following schema:

| Column Name           | Data Type   | Value                             |
|-----------------------|-------------|-----------------------------------|
| database_name | VARCHAR | name of the database  |
| database_size | VARCHAR | database size in 1000 byte increments (i.e. Kilobytes) |
| block_size | BIGINT | _not currently returned_ |
| used_blocks | BIGINT | _not currently returned_ |
| total_blocks | BIGINT | _not currently returned_ |
| free_blocks | BIGINT | _not currently returned_ |
| wal_size | VARCHAR | _not currently returned_ |
| memory_usage | VARCHAR | _not currently returned_ |
| memory_limit | VARCHAR | _not currently returned_ |

## Example Usage

```sql
PRAGMA database_size;
```

Example result:

| database_name | database_size | block_size | used_blocks | total_blocks | free_blocks | wal_size | memory_usage | memory_limit |
|---------------|---------------|------------|-------------|--------------|-------------|----------|--------------|--------------|
| my_db   | 153.9 GiB         | NULL       | NULL        | NULL         | NULL        | NULL     | NULL         | NULL         |
| another_database  | 3.1 TiB         | NULL       | NULL        | NULL         | NULL        | NULL     | NULL         | NULL         |

In some cases, you may want to filter the dataset, in which case you can use a tabular function in your `FROM` clause. An example is shown below:

```sql
SELECT *
FROM pragma_database_size()
WHERE database_name = 'my_db'
```

---

Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/introduction

# Introduction to MD_INFORMATION_SCHEMA


> Introduction to MD_INFORMATION_SCHEMA

The MotherDuck `MD_INFORMATION_SCHEMA` views are read-only, system-defined views that provide metadata information
about your MotherDuck objects.
The following table lists all `MD_INFORMATION_SCHEMA` views that you can query to retrieve metadata information:

| Resource Type   | MD_INFORMATION_SCHEMA View       |
|-----------------|----------------------------------|
| Database        | [DATABASES](databases.md)                        |
| Database Size   | [PRAGMA database_size](database_size.md)                        |
| Database Shares | [OWNED_SHARES](owned_shares.md)<br/> [SHARED_WITH_ME](shared_with_me.md)<br/> |
| Database Snapshots | [DATABASE_SNAPSHOTS](database_snapshots.md)                        |
| Storage         | [STORAGE_INFO](storage_info.md) _includes history_                       |
| Queries         | [RECENT_QUERIES](recent_queries.md)                        |
| Queries         | [QUERY_HISTORY](query_history.md)                        |

## Example usage

```sql
-- list all databases you created
from md_information_schema.databases;
-- list all shares you created
from md_information_schema.owned_shares;
-- select specific columns
select name, url, access, visibility from md_information_schema.owned_shares;
-- set md_information_schema as the current database
use md_information_schema;
-- list all the views in md_information_schema
show tables;
```

---

## 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%2Fmd_information_schema%2F&page_title=MotherDuck%20Documentation%20-%20MD_INFORMATION_SCHEMA&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.