Connect to a database.

Establishes a pooled connection to a database. Required before any other database operations. Returns a connection_id for subsequent calls.

Two connection methods:

• connection_name: Use a named connection from dbmcp.toml config file

• sqlalchemy_url: Connect directly with a SQLAlchemy URL (e.g., 'postgresql://user:pass@host/db')

Provide exactly one of connection_name or sqlalchemy_url.

MSSQL URL query parameters (mssql+pyodbc://...):

• authentication_method: sql | windows | azure_ad | azure_ad_integrated (default: sql when credentials are present, else windows)

• trust_server_cert: true | false (default: false)

• tenant_id: Azure AD tenant (optional)

Example (MSSQL): mssql+pyodbc://user:pass@host/db?authentication_method=sql&trust_server_cert=true

Args: connection_name: Named connection from config file (optional) sqlalchemy_url: SQLAlchemy connection URL (optional)

Returns: TOON-encoded string with connection details:

    status: "success" | "error"
    connection_id: string              // on success only
    message: string                    // on success only
    dialect: string                    // on success only
    schema_count: int                  // on success only
    has_cached_docs: bool              // on success only
    error_message: string              // on error only

Execute a SQL SELECT query and return results.

Executes ad-hoc SELECT queries with automatic row limiting for safety. Write operations (INSERT, UPDATE, DELETE) are blocked. Results are returned as a structured JSON with columns and rows.

Large text values (>1000 chars) and binary data are automatically truncated to keep responses token-efficient.

Args: connection_id: Connection ID from connect_database query_text: SQL query to execute (SELECT only) row_limit: Maximum rows to return, 1-10000 (default: 1000)

Returns: TOON-encoded string with query results:

    status: "success" | "blocked" | "error"
    query_id: string                   // on success only
    query_type: string                 // on success only
    columns: list of string            // on success only
    rows: list of object               // on success only
    rows_returned: int                 // on success only
    rows_available: int                // on success only
    limited: bool                      // on success only
    execution_time_ms: float           // on success only
    error_message: string              // on error/blocked only

Discover potential foreign key relationships for a source column.

EXPERIMENTAL — Results are based on common heuristics but have not been battle-tested for utility. They may contain false positives or exclude valid candidates. Use as a starting point for investigation, not as definitive answers.

Searches for target columns that could be the referenced side of a foreign key relationship. Matches by compatible data type. By default only considers target columns that are PK candidates (constraint-backed or structurally unique); set pk_candidates_only=False to broaden the search to all type-compatible columns. Optionally computes value overlap between source and target via SQL INTERSECT.

Args: connection_id: Connection ID from connect_database table_name: Source table name. May be dotted (e.g. 'schema.table' or 'catalog.schema.table') and is resolved against the dialect. column_name: Source column name schema_name: Source schema name. Defaults to the dialect's default schema (e.g. 'dbo' on MSSQL) when omitted. target_schema: Filter targets to this schema. Defaults to source schema. target_tables: Explicit list of target table names target_table_pattern: SQL LIKE pattern for target table names pk_candidates_only: Only compare against PK-candidate columns (default: True) include_overlap: Compute value overlap metrics (default: False) limit: Maximum candidates to return, 0 = no limit (default: 100) catalog: Optional Databricks catalog name. Rejected on non-Databricks dialects (returns an error response). On Databricks the catalog is threaded end-to-end (IDENT-08): the existence check, source-column type reflection, and FK search run against the requested catalog via catalog-scoped reflection (cross-catalog supported — no default-catalog binding required).

Returns: TOON-encoded string with status, source metadata, candidates list, and search info:

    status: "success" | "error"
    source: object                     // on success only
        column_name: string
        table_name: string
        schema_name: string
        data_type: string
    candidates: list                   // on success only
        source_column: string
        source_table: string
        source_schema: string
        source_data_type: string
        target_column: string
        target_table: string
        target_schema: string
        target_data_type: string
        target_is_primary_key: bool
        target_is_unique: bool
        target_is_nullable: bool
        target_has_index: bool
        overlap_count: int             // only when include_overlap=True
        overlap_percentage: float      // only when include_overlap=True
    total_found: int                   // on success only
    was_limited: bool                  // on success only
    search_scope: string               // on success only
    type_incompatible_skipped: int     // only when > 0 (type-incompatible targets skipped)
    error_message: string              // on error only

Error conditions: - Invalid connection_id: returns status "error" with error_message - Table not found: returns status "error" with error_message - Column not found: returns status "error" with error_message - No candidates: returns status "success" with empty candidates list

Identify columns that meet primary key candidacy criteria.

EXPERIMENTAL — Results are based on common heuristics but have not been battle-tested for utility. They may contain false positives or exclude valid candidates. Use as a starting point for investigation, not as definitive answers.

Discovers PK candidates via two approaches:

1. Constraint-backed: Columns with declared PK or UNIQUE constraints

2. Structural: Columns that are unique, non-null, and match the type filter

Does not detect composite keys. Structural uniqueness checks query the full table and may be slow on very large tables.

Args: connection_id: Connection ID from connect_database table_name: Table to search for PK candidates. May be dotted (e.g. 'schema.table' or 'catalog.schema.table') and is resolved against the dialect. schema_name: Schema name. Defaults to the dialect's default schema (e.g. 'dbo' on MSSQL) when omitted. type_filter: SQL types considered for structural PK candidacy. Default: ["int", "bigint", "smallint", "tinyint", "uniqueidentifier"]. Set to empty list to disable type filtering. catalog: Optional Databricks catalog name. Rejected on non-Databricks dialects (returns an error response). On Databricks the catalog is threaded end-to-end (IDENT-08): the existence check and PK discovery run against the requested catalog via catalog-scoped reflection (cross-catalog supported — no default-catalog binding required).

Returns: TOON-encoded string with status, table/schema metadata, and candidates list:

    status: "success" | "error"
    table_name: string                 // on success only
    schema_name: string                // on success only
    candidates: list                   // on success only
        column_name: string
        data_type: string
        is_constraint_backed: bool
        constraint_type: "PRIMARY KEY" | "UNIQUE" | null
        is_unique: bool                // all values distinct
        is_non_null: bool              // no nulls
        is_pk_type: bool               // data_type matches type_filter
    error_message: string              // on error only

Error conditions: - Invalid connection_id: returns status "error" with error_message - Table not found: returns status "error" with error_message - No candidates found: returns status "success" with empty candidates list

Retrieve per-column statistical profiles for a table.

EXPERIMENTAL — Statistics are based on common practices but have not been battle-tested for utility. Use as a starting point for investigation, not as definitive answers.

Computes row counts, distinct counts, null counts/percentages, and type-specific statistics for each column. Numeric columns get min/max/mean/stddev. Datetime columns get min/max dates, range in days, and whether a time component is present. String columns get min/max/avg length and a sample of top frequent values.

Args: connection_id: Connection ID from connect_database table_name: Name of the table. May be dotted (e.g. 'schema.table' or 'catalog.schema.table') and is resolved against the dialect. schema_name: Schema name. Defaults to the dialect's default schema (e.g. 'dbo' on MSSQL) when omitted. columns: Explicit list of column names to analyze (takes precedence over pattern) column_pattern: SQL LIKE pattern to filter column names (e.g., '%_id') sample_size: Number of top frequent value samples for string columns (default: 10) catalog: Optional Databricks catalog name. Rejected on non-Databricks dialects (returns an error response). On Databricks the catalog is threaded end-to-end: the existence check and column statistics are computed against the requested catalog (cross-catalog supported via catalog-scoped reflection — IDENT-08).

Returns: TOON-encoded string with status, table/schema metadata, and column statistics:

    status: "success" | "error"
    table_name: string                 // on success only
    schema_name: string                // on success only
    total_columns_analyzed: int        // on success only
    columns: list                      // on success only
        column_name: string
        data_type: string
        total_rows: int
        distinct_count: int
        distinct_count_approximate: bool   // true = HLL-approximate (Databricks fast path)
        null_count: int
        null_percentage: float
        numeric_stats: object          // numeric columns only
            min_value: float | null
            max_value: float | null
            mean_value: float | null
            std_dev: float | null
        datetime_stats: object         // datetime columns only
            min_date: ISO 8601 string | null
            max_date: ISO 8601 string | null
            date_range_days: int | null
            has_time_component: bool
        string_stats: object           // string columns only
            min_length: int | null
            max_length: int | null
            avg_length: float | null
            sample_values: list of [string, int] pairs
    error_message: string              // on error only

Error conditions: - Invalid connection_id: returns status "error" with error_message - Table not found: returns status "error" with error_message - Column not found (explicit list): returns status "error" with error_message - No columns match pattern: returns status "success" with empty columns list

Retrieve sample data from a table.

Returns representative sample rows from a table with support for multiple sampling strategies. Automatically truncates large text (>1000 chars) and binary data (shows first 32 bytes as hex) to keep responses token-efficient.

Args: connection_id: Connection ID from connect_database table_name: Name of the table. May be dotted (e.g. 'schema.table' or 'catalog.schema.table') and is resolved against the dialect. schema_name: Schema name. Defaults to the dialect's default schema (e.g. 'dbo' on MSSQL) when omitted. sample_size: Number of rows to return, 1-1000 (default: 5) sampling_method: Sampling strategy - 'top', 'tablesample', or 'modulo' (default: 'top') - 'top': Fast SELECT TOP N (not representative, just first N rows) - 'tablesample': SQL Server statistical sampling (more representative) - 'modulo': Deterministic sampling using modulo on row number (repeatable) columns: Optional list of column names to include (default: all columns) catalog: Optional Databricks catalog name. Rejected on non-Databricks dialects (returns an error response).

Returns: TOON-encoded string with sample rows and metadata:

    status: "success" | "error"
    sample_id: string                  // on success only
    table_id: string                   // on success only
    sample_size: int                   // on success only
    actual_rows_returned: int          // on success only
    sampling_method: "top" | "tablesample" | "modulo"  // on success only
    rows: list of object               // on success only
    truncated_columns: list of string  // on success only
    sampled_at: ISO 8601 string        // on success only
    error_message: string              // on error only

Get detailed schema for a specific table.

Returns complete table metadata including columns, data types, constraints, indexes, and declared foreign key relationships.

Args: connection_id: Connection ID from connect_database table_name: Name of the table. May be dotted (e.g. 'schema.table' or 'catalog.schema.table') and is resolved against the dialect. schema_name: Schema name. Defaults to the dialect's default schema (e.g. 'dbo' on MSSQL) when omitted. include_indexes: Include index information (default: True) include_relationships: Include declared foreign keys (default: True) catalog: Optional Databricks catalog name. Overrides the connection's default catalog. Rejected on non-Databricks dialects (raises an error).

Returns: TOON-encoded string with table schema details:

    status: "success" | "error"
    table: object                          // on success only
        table_name: string
        schema_name: string
        columns: list
            column_name: string
            ordinal_position: int
            data_type: string
            max_length: int | null
            is_nullable: bool
            default_value: string | null
            is_identity: bool
            is_computed: bool
            is_primary_key: bool
            is_foreign_key: bool
        indexes: list                      // if include_indexes=True
            index_name: string
            is_unique: bool
            is_primary_key: bool
            is_clustered: bool
            columns: list of string
            included_columns: list of string
        foreign_keys: list                 // if include_relationships=True
            constraint_name: string | null
            source_columns: list of string
            target_schema: string
            target_table: string
            target_columns: list of string
    error_message: string                  // on error only

List all schemas in the connected database.

Returns schemas with table and view counts, sorted by table count descending. Excludes system schemas (sys, INFORMATION_SCHEMA, guest).

Args: connection_id: Connection ID from connect_database catalog: Optional Databricks catalog name. Overrides the connection's default catalog. If omitted on a Databricks connection, the connection's configured default catalog is used (SHOW SCHEMAS IN). Rejected on non-Databricks dialects (raises an error).

Returns: TOON-encoded string with schema list:

    status: "success" | "error"
    total_schemas: int                 // on success only
    schemas: list                      // on success only
        schema_name: string
        table_count: int
        view_count: int
    error_message: string              // on error only

Error conditions: - Invalid connection_id: returns status "error" with error_message

List tables in specified schema(s) with row counts and metadata.

Efficiently retrieves table metadata using SQL Server DMVs. Supports filtering by schema, name pattern, and minimum row count. Supports pagination via offset parameter. Supports filtering by object type to include/exclude views.

Args: connection_id: Connection ID from connect_database schema_filter: List of schema names to include (empty = all schemas) name_pattern: Table name filter using SQL LIKE pattern (e.g., 'Customer%') min_row_count: Minimum row count threshold to filter tables sort_by: Sort criterion - 'name', 'row_count', or 'last_modified' (default: 'row_count') sort_order: Sort order - 'asc' or 'desc' (default: 'desc') limit: Maximum tables to return, 1-1000 (default: 100) offset: Number of results to skip for pagination (default: 0) object_type: Filter by type - 'table', 'view', or None for all (default: None) output_mode: 'summary' (names+row counts) or 'detailed' (includes columns) (default: 'summary') catalog: Optional Databricks catalog name. Overrides the connection's default catalog. Rejected on non-Databricks dialects (raises an error).

Returns: TOON-encoded string with table list and pagination metadata:

    status: "success" | "error"
    returned_count: int                // on success only
    total_count: int                   // on success only
    offset: int                        // on success only
    limit: int                         // on success only
    has_more: bool                     // on success only
    tables: list                       // on success only
        schema_name: string
        table_name: string
        table_type: "table" | "view"
        row_count: int
        has_primary_key: bool
        last_modified: ISO 8601 string | null
        access_denied: bool
        columns: list              // detailed mode only
    error_message: string          // on error only