PROMPT_SCHEMA

Describe contents of a database

The prompt_schema table function allows MotherDuck AI to analyze and describe the contents of your current database in plain English. This feature helps you understand the structure, purpose, and relationships between tables in your database without having to manually inspect each table's schema.

tip

This function is particularly useful when working with unfamiliar databases or when you need a high-level overview of a complex database structure.

Syntax

CALL prompt_schema([include_tables=['<table_name>', '<table_name>']]);

Parameters

Parameter	Required	Description
include_tables	No	Array of table names to consider for analysis (defaults to all tables in current database)

Example usage

Here are several examples using MotherDuck's sample data database.

Describing the entire database

CALL prompt_schema();

Example output

When you run a prompt_schema query, you'll receive a single-column table with a detailed description:

summary
The database contains tables related to ambient air quality data, Stack Overflow survey results, NYC taxi and service requests, rideshare data, movie information with embeddings, and Hacker News articles, capturing a wide range of information from environmental metrics to user-generated content and transportation data.

Describing specific tables

CALL prompt_schema(include_tables=['hn.hacker_news', 'hn.stories']);

summary
The database contains information about Hacker News posts, including details such as the title, URL, content, author, score, time of posting, type of post, and various identifiers and status flags.

How it works

The prompt_schema function processes your database in several steps:

1. Schema extraction: examines the structure of tables, including column names and data types
2. Data sampling: analyzes sample data to understand the content and purpose of each table
3. Relationship detection: identifies potential relationships between tables based on column names and values
4. Domain recognition: categorizes tables into domains or subject areas based on their content
5. Summary generation: creates a human-readable description of the database structure and purpose

Best practices

For the best results with prompt_schema:

1. Focus on relevant tables: use the include_tables parameter to analyze specific parts of large databases
2. Run on updated databases: ensure your database is up-to-date for the most accurate description
3. Use for documentation: save the output as part of your database documentation
4. Combine with other tools: use alongside DESCRIBE and SHOW commands for complete understanding
5. Share with team members: use the output to help new team members understand the database structure

Notes

MotherDuck AI operates on your current database by evaluating the schemas and contents of the database. You can specify which tables and columns should be considered using the optional include_tables parameter. By default, all tables in the current database are considered.

To point MotherDuck AI at a specific database, execute the USE database command (learn more about switching databases).

These capabilities are provided by MotherDuck's integration with OpenAI. For availability and pricing, see MotherDuck's Pricing Model.

If you need higher usage limits or have specific requirements, please reach out to the Slack support channel or email support@motherduck.com.