# Core tools

> Query your databases, explore schemas and shares, search the catalog, and ask documentation questions through the MotherDuck MCP server.

## Included pages

- [list_databases](https://motherduck.com/docs/sql-reference/mcp/core/list-databases): List all databases in your MotherDuck account
- [list_shares](https://motherduck.com/docs/sql-reference/mcp/core/list-shares): List database shares that have been shared with you
- [list_tables](https://motherduck.com/docs/sql-reference/mcp/core/list-tables): List tables and views in a MotherDuck database
- [list_columns](https://motherduck.com/docs/sql-reference/mcp/core/list-columns): List columns of a table or view with types and comments
- [search_catalog](https://motherduck.com/docs/sql-reference/mcp/core/search-catalog): Fuzzy search across databases, schemas, tables, columns, and shares
- [query](https://motherduck.com/docs/sql-reference/mcp/core/query): Execute SQL queries against MotherDuck databases
- [query_rw](https://motherduck.com/docs/sql-reference/mcp/core/query-rw): Execute SQL queries that can modify data or schema in MotherDuck
- [ask_docs_question](https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question): Ask questions about DuckDB or MotherDuck documentation

Source: https://motherduck.com/docs/category/core-tools

---

## list_databases

Source: https://motherduck.com/docs/sql-reference/mcp/core/list-databases

> List all databases in your MotherDuck account

# list_databases

List all databases in your MotherDuck account with their names and types.

## Description

The `list_databases` tool returns all databases accessible to your MotherDuck account, including both owned databases and attached shared databases. This is useful for discovering what data is available before running queries.

## Input parameters

This tool takes no input parameters.

## Output schema

```json
{
  "success": boolean,
  "databases": [                 // List of databases (on success)
    {
      "alias": string,           // Database name/alias
      "is_attached": boolean,    // Whether the database is currently attached
      "type": string             // Database type (e.g., "motherduck", "memory")
    }
  ],
  "error": string                // Error message (on failure)
}
```

## Example usage

**List available databases:**

```text
What databases do I have access to?
```

The AI assistant will call the tool with no parameters.

## Success response example

```json
{
  "success": true,
  "databases": [
    {
      "alias": "my_db",
      "is_attached": true,
      "type": "motherduck"
    },
    {
      "alias": "analytics",
      "is_attached": true,
      "type": "motherduck"
    },
    {
      "alias": "shared_sales_data",
      "is_attached": true,
      "type": "motherduck"
    }
  ]
}
```

---

## list_shares

Source: https://motherduck.com/docs/sql-reference/mcp/core/list-shares

> List database shares that have been shared with you

# list_shares

List all database [shares](/key-tasks/sharing-data/sharing-overview) that have been shared with you.

## Description

The `list_shares` tool returns all database shares that have been shared with you by other users. Each share includes its name and URL, which can be used to attach the share as a database using the `query` tool.

To attach a share, execute: `ATTACH '<share_url>' AS my_alias;`

To detach a share: `DETACH <database_alias>;`

## Input parameters

This tool takes no input parameters.

## Output schema

```json
{
  "success": boolean,
  "shares": [                    // List of shares (on success)
    {
      "name": string,            // Share name
      "url": string              // Share URL for attaching
    }
  ],
  "error": string                // Error message (on failure)
}
```

## Example usage

**List available shares:**

```text
What shares have been shared with me?
```

The AI assistant will call the tool with no parameters.

**Attach a share after listing:**

```text
Attach the sales_data share so I can query it
```

After getting the share URL from `list_shares`, the AI will use the `query` tool:

```json
{
  "database": "my_db",
  "sql": "ATTACH 'md:_share/org123/sales_data' AS sales_data"
}
```

## Success response example

```json
{
  "success": true,
  "shares": [
    {
      "name": "sales_data",
      "url": "md:_share/org123/sales_data"
    },
    {
      "name": "product_catalog",
      "url": "md:_share/org456/product_catalog"
    },
    {
      "name": "analytics_benchmark",
      "url": "md:_share/org789/analytics_benchmark"
    }
  ]
}
```

## Empty response example

When no shares have been shared with you:

```json
{
  "success": true,
  "shares": []
}
```

## Related

- [Sharing Overview](/key-tasks/sharing-data/sharing-overview) - Learn about MotherDuck's data sharing capabilities
- [Managing Shares](/key-tasks/sharing-data/managing-shares) - How to create and manage shares

---

## list_tables

Source: https://motherduck.com/docs/sql-reference/mcp/core/list-tables

> List tables and views in a MotherDuck database

# list_tables

List all tables and views in a MotherDuck database with their comments.

## Description

The `list_tables` tool returns all tables and views in a specified database, including their schema, type (table or view), and any comments that have been added. You can optionally filter by schema.

## Input parameters

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `database` | string | Yes | Database name to list tables from |
| `schema` | string | No | Schema name to filter by (defaults to all schemas) |

## Output schema

```json
{
  "success": boolean,
  "database": string,            // Database name
  "schema": string,              // Schema filter used ("all" if not specified)
  "tables": [                    // List of tables and views (on success)
    {
      "schema": string,          // Schema name
      "name": string,            // Table or view name
      "type": "table" | "view",  // Object type
      "comment": string | null   // Table/view comment if set
    }
  ],
  "tableCount": number,          // Number of tables
  "viewCount": number,           // Number of views
  "error": string                // Error message (on failure)
}
```

## Example usage

**List all tables in a database:**

```text
Show me all tables in my_database
```

The AI assistant will call the tool with:

```json
{
  "database": "my_database"
}
```

**List tables in a specific schema:**

```text
What tables are in the staging schema of analytics_db?
```

```json
{
  "database": "analytics_db",
  "schema": "staging"
}
```

## Success response example

```json
{
  "success": true,
  "database": "my_database",
  "schema": "all",
  "tables": [
    {
      "schema": "main",
      "name": "customers",
      "type": "table",
      "comment": "Customer master data"
    },
    {
      "schema": "main",
      "name": "orders",
      "type": "table",
      "comment": "Order transactions"
    },
    {
      "schema": "main",
      "name": "monthly_sales",
      "type": "view",
      "comment": "Aggregated monthly sales view"
    },
    {
      "schema": "staging",
      "name": "raw_events",
      "type": "table",
      "comment": null
    }
  ],
  "tableCount": 3,
  "viewCount": 1
}
```

## Error response example

```json
{
  "success": false,
  "error": "Catalog Error: Database \"nonexistent_db\" does not exist"
}
```

---

## list_columns

Source: https://motherduck.com/docs/sql-reference/mcp/core/list-columns

> List columns of a table or view with types and comments

# list_columns

List all columns of a table or view with their types and comments.

## Description

The `list_columns` tool returns detailed column information for a specified table or view, including data types, nullability, and any comments. This is useful for understanding table structure before writing queries.

## Input parameters

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `table` | string | Yes | Table or view name |
| `database` | string | Yes | Database name |
| `schema` | string | No | Schema name (defaults to `main`) |

## Output schema

```json
{
  "success": boolean,
  "database": string,            // Database name
  "schema": string,              // Schema name
  "table": string,               // Table or view name
  "objectType": "table" | "view", // Whether it's a table or view
  "columns": [                   // List of columns (on success)
    {
      "name": string,            // Column name
      "type": string,            // Data type
      "nullable": boolean,       // Whether nulls are allowed
      "comment": string | null   // Column comment if set
    }
  ],
  "columnCount": number,         // Number of columns
  "error": string                // Error message (on failure)
}
```

## Example usage

**Get columns for a table:**

```text
What columns does the customers table have in my_database?
```

The AI assistant will call the tool with:

```json
{
  "table": "customers",
  "database": "my_database"
}
```

**Get columns in a specific schema:**

```text
Show me the schema of staging.raw_events in analytics_db
```

```json
{
  "table": "raw_events",
  "database": "analytics_db",
  "schema": "staging"
}
```

## Success response example

```json
{
  "success": true,
  "database": "my_database",
  "schema": "main",
  "table": "customers",
  "objectType": "table",
  "columns": [
    {
      "name": "id",
      "type": "INTEGER",
      "nullable": false,
      "comment": "Primary key"
    },
    {
      "name": "email",
      "type": "VARCHAR",
      "nullable": false,
      "comment": "Customer email address"
    },
    {
      "name": "name",
      "type": "VARCHAR",
      "nullable": true,
      "comment": "Full name"
    },
    {
      "name": "created_at",
      "type": "TIMESTAMP",
      "nullable": false,
      "comment": null
    },
    {
      "name": "metadata",
      "type": "JSON",
      "nullable": true,
      "comment": "Additional customer attributes"
    }
  ],
  "columnCount": 5
}
```

## Error response example

```json
{
  "success": false,
  "error": "Catalog Error: Table \"nonexistent_table\" does not exist"
}
```

---

## search_catalog

Source: https://motherduck.com/docs/sql-reference/mcp/core/search-catalog

> Fuzzy search across databases, schemas, tables, columns, and shares

# search_catalog

Search the catalog for databases, schemas, tables, columns, and shares using fuzzy matching.

## Description

The `search_catalog` tool performs fuzzy search across your entire MotherDuck catalog. It finds matching objects by name using partial matching, supporting underscores, dots, and multi-word queries. This is useful for discovering available data when you don't know exact names.

The search uses Jaro-Winkler similarity scoring and returns results ranked by relevance. Results are limited per category to provide a balanced view across different object types.

## Input parameters

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `query` | string | Yes | Search term to find in object names (supports partial matching, underscores, dots) |
| `object_types` | string[] | No | Filter results to specific types: `"database"`, `"schema"`, `"table"`, `"column"`, `"share"` |

## Output schema

```json
{
  "success": boolean,
  "query": string,               // Search query used
  "resultCount": number,         // Total results found
  "results": [                   // Search results (on success)
    {
      "type": "database" | "schema" | "table" | "column" | "share",
      "name": string,            // Object name
      "fullyQualifiedName": string, // Full path (e.g., "db.schema.table.column")
      "database": string | null, // Database (null for shares)
      "schema": string | null,   // Schema (null for databases/shares)
      "table": string | null,    // Table (only for columns)
      "dataType": string | null, // Data type (columns) or URL (shares)
      "comment": string | null,  // Object comment if set
      "relevanceScore": number   // Match score 0-1 (higher is better)
    }
  ],
  "error": string,               // Error message (on failure)
  "errorType": string            // Error type (on failure)
}
```

## Result limits

Results are limited per object type to provide balanced coverage:
- Shares: 10 results
- Columns: 40 results
- Tables: 30 results
- Schemas: 20 results
- Databases: 20 results

Maximum total results: 100

## Example usage

**Search for tables with "sales" in the name:**

```text
Find all tables related to sales data
```

The AI assistant will call the tool with:

```json
{
  "query": "sales"
}
```

**Search only for columns:**

```text
Find columns containing "email"
```

```json
{
  "query": "email",
  "object_types": ["column"]
}
```

**Search with qualified name:**

```text
Find anything matching analytics.events
```

```json
{
  "query": "analytics.events"
}
```

## Success response example

```json
{
  "success": true,
  "query": "sales",
  "resultCount": 8,
  "results": [
    {
      "type": "table",
      "name": "sales_data",
      "fullyQualifiedName": "analytics.main.sales_data",
      "database": "analytics",
      "schema": "main",
      "table": null,
      "dataType": null,
      "comment": "Daily sales transactions",
      "relevanceScore": 0.95
    },
    {
      "type": "table",
      "name": "monthly_sales",
      "fullyQualifiedName": "analytics.main.monthly_sales",
      "database": "analytics",
      "schema": "main",
      "table": null,
      "dataType": null,
      "comment": null,
      "relevanceScore": 0.89
    },
    {
      "type": "column",
      "name": "total_sales",
      "fullyQualifiedName": "analytics.main.revenue.total_sales",
      "database": "analytics",
      "schema": "main",
      "table": "revenue",
      "dataType": "DECIMAL(18,2)",
      "comment": "Total sales amount",
      "relevanceScore": 0.87
    },
    {
      "type": "share",
      "name": "regional_sales_share",
      "fullyQualifiedName": "regional_sales_share",
      "database": "regional_sales_share",
      "schema": null,
      "table": null,
      "dataType": "md:_share/org123/regional_sales_share",
      "comment": null,
      "relevanceScore": 0.82
    }
  ]
}
```

## Error response example

```json
{
  "success": false,
  "error": "Search query cannot be empty",
  "errorType": "ValidationError"
}
```

---

## query

Source: https://motherduck.com/docs/sql-reference/mcp/core/query

> Execute SQL queries against MotherDuck databases

# query

Execute **read-only** SQL queries against MotherDuck databases.

## Description

The `query` tool executes SQL queries against your MotherDuck databases. For cross-database queries, use fully qualified names: `database.schema.table` (or `database.table` for the main schema).

`query` is for read-only SQL. Operations that modify data, schema, or account settings, or trigger side effects, are rejected. For SQL that can change data or schema, use [`query_rw`](/sql-reference/mcp/core/query-rw/).

## Input parameters

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `database` | string | Yes | Database name to query |
| `sql` | string | Yes | DuckDB SQL query to execute |

## Output schema

```json
{
  "success": boolean,
  "columns": string[],           // Column names (on success)
  "columnTypes": string[],       // Column types (on success)
  "rows": any[][],               // Query results (on success)
  "rowCount": number,            // Number of rows returned (on success)
  "error": string,               // Error message (on failure)
  "errorType": string            // Error type (on failure)
}
```

## Limits

- **Result limit:** Maximum 2,048 rows and 50,000 characters. Results exceeding these limits will be truncated with a truncation message.
- **Query timeout:** 55 seconds, to stay within common client timeouts. Queries exceeding this limit will be cancelled server-side and the tool will respond with an error message.

## Example usage

**Simple query:**

```text
Query the top 5 customers by total orders from my_database
```

The AI assistant will call the tool with:

```json
{
  "database": "my_database",
  "sql": "SELECT customer_name, COUNT(*) as order_count FROM orders GROUP BY customer_name ORDER BY order_count DESC LIMIT 5"
}
```

**Cross-database query:**

```text
Join the users table from auth_db with orders from sales_db
```

```json
{
  "database": "auth_db",
  "sql": "SELECT u.name, o.order_id, o.amount FROM auth_db.main.users u JOIN sales_db.main.orders o ON u.id = o.user_id LIMIT 100"
}
```

## Success response example

```json
{
  "success": true,
  "columns": ["customer_name", "order_count"],
  "columnTypes": ["VARCHAR", "BIGINT"],
  "rows": [
    ["Acme Corp", 150],
    ["TechStart Inc", 89],
    ["Global Services", 72]
  ],
  "rowCount": 3
}
```

## Error response example

```json
{
  "success": false,
  "error": "Query is not read-only",
  "errorType": "ForbiddenQueryError"
}
```

---

## query_rw

Source: https://motherduck.com/docs/sql-reference/mcp/core/query-rw

> Execute SQL queries that can modify data or schema in MotherDuck

# query_rw

Execute SQL queries that can modify data or schema in MotherDuck.

## Description

The `query_rw` tool executes SQL against your MotherDuck databases, including operations that change data or schema. For cross-database queries, use fully qualified names: `database.schema.table` (or `database.table` for the main schema).

## Input parameters

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `database` | string | No | Database context for the query. Required when the statement targets database objects. Optional for account-level operations. |
| `sql` | string | Yes | DuckDB SQL statement to execute |

## Output schema

Same as [`query`](/sql-reference/mcp/core/query/):

```json
{
  "success": boolean,
  "columns": string[],           // Column names (on success)
  "columnTypes": string[],       // Column types (on success)
  "rows": any[][],               // Query results (on success)
  "rowCount": number,            // Number of rows returned (on success)
  "error": string,               // Error message (on failure)
  "errorType": string            // Error type (on failure)
}
```

## Limits

- **Result limit:** Maximum 2,048 rows and 50,000 characters. Results exceeding these limits will be truncated with a truncation message.
- **Query timeout:** 55 seconds. Queries exceeding this limit will be cancelled server-side and the tool will respond with an error message.

## Example usage

**Insert rows:**

```text
Insert a new customer 'Acme Corp' with id 100 into my_database.customers
```

```json
{
  "database": "my_database",
  "sql": "INSERT INTO customers (id, name) VALUES (100, 'Acme Corp')"
}
```

**Update and delete:**

```text
In my_database, set status to 'shipped' for all orders in the orders table where status is 'pending', then delete the old log entries from audit_log
```

The AI assistant can call `query_rw` with the appropriate UPDATE and DELETE statements (or multiple calls if the client requires one statement per call).

**Create table:**

```text
Create a table my_database.main.events with columns id (BIGINT), name (VARCHAR), created_at (TIMESTAMP)
```

```json
{
  "database": "my_database",
  "sql": "CREATE TABLE main.events (id BIGINT, name VARCHAR, created_at TIMESTAMP)"
}
```

**Account-level operations (database optional):**

```text
Create a new database called reporting
```

```json
{
  "sql": "CREATE DATABASE reporting"
}
```

For account-level operations, omit `database` and pass only `sql`.

:::tip Read-only access
To restrict the MCP server so the AI can only read data, see [Restricting to read-only access](/key-tasks/ai-and-motherduck/securing-read-only-access/).
:::

---

## ask_docs_question

Source: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question

> Ask questions about DuckDB or MotherDuck documentation

# ask_docs_question

Ask a question about DuckDB or MotherDuck and get answers from official documentation.

## Description

The `ask_docs_question` tool queries the official DuckDB and MotherDuck documentation to answer questions about SQL syntax, features, best practices, and more. This is useful when you need help with DuckDB-specific SQL syntax or MotherDuck features.

The tool uses MotherDuck's documentation assistant to provide accurate answers based on official documentation sources.

## Input parameters

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `question` | string | Yes | Question about DuckDB or MotherDuck |

## Output schema

```json
{
  "success": boolean,
  "question": string,            // Original question (on success)
  "answer": string,              // Documentation-based answer (on success)
  "sources": string,             // Source references (optional, on success)
  "error": string                // Error message (on failure)
}
```

## Example usage

**Ask about DuckDB syntax:**

```text
How do I use window functions in DuckDB?
```

The AI assistant will call the tool with:

```json
{
  "question": "How do I use window functions in DuckDB?"
}
```

**Ask about MotherDuck features:**

```text
How do I create a share in MotherDuck?
```

```json
{
  "question": "How do I create a share in MotherDuck?"
}
```

**Ask about data types:**

```text
What's the difference between LIST and ARRAY types in DuckDB?
```

```json
{
  "question": "What's the difference between LIST and ARRAY types in DuckDB?"
}
```

## Success response example

```json
{
  "success": true,
  "question": "How do I use window functions in DuckDB?",
  "answer": "Window functions in DuckDB allow you to perform calculations across a set of rows related to the current row. Here's how to use them:\n\n**Basic syntax:**\n```sql\nSELECT \n  column,\n  SUM(value) OVER (PARTITION BY category ORDER BY date) as running_total\nFROM table_name;\n```\n\n**Common window functions:**\n- `ROW_NUMBER()` - assigns unique row numbers\n- `RANK()` and `DENSE_RANK()` - ranking with/without gaps\n- `LAG()` and `LEAD()` - access previous/next rows\n- `FIRST_VALUE()` and `LAST_VALUE()` - first/last value in window\n\n**Using QUALIFY:**\nDuckDB supports the QUALIFY clause to filter window function results:\n```sql\nSELECT *\nFROM sales\nQUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) = 1;\n```\n\nThis returns only the top sale per region.",
  "sources": "https://duckdb.org/docs/sql/window_functions"
}
```

## Tips for good questions

- Be specific about what you want to know
- Include context about what you're trying to accomplish
- Mention specific functions or features if known

---