This is an automated email from the ASF dual-hosted git repository.

Lee-W pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/airflow.git


The following commit(s) were added to refs/heads/main by this push:
     new 35eedb3749b Let SQLToolset agents see table comments and search large 
schemas (#76799)
35eedb3749b is described below

commit 35eedb3749bb54047b1a5a329126a5104f8a7bef
Author: Wei Lee <[email protected]>
AuthorDate: Wed Nov 18 00:39:07 2026 +0800

    Let SQLToolset agents see table comments and search large schemas (#76799)
---
 providers/common/ai/docs/agent_security.rst        |   5 +-
 .../ai/docs/toolsets/progressive_disclosure.rst    |   3 +-
 providers/common/ai/docs/toolsets/sql.rst          | 232 ++++++++++-
 providers/common/ai/pyproject.toml                 |   4 +-
 .../airflow/providers/common/ai/toolsets/sql.py    | 203 ++++++++-
 .../providers/common/ai/utils/query_results.py     |  86 +++-
 .../providers/common/ai/utils/sql_search.py        |  85 ++++
 .../unit/common/ai/toolsets/test_datafusion.py     |   5 +
 .../ai/tests/unit/common/ai/toolsets/test_sql.py   | 452 ++++++++++++++++++++-
 .../unit/common/ai/utils/test_query_results.py     | 101 +++++
 .../tests/unit/common/ai/utils/test_sql_search.py  |  70 ++++
 .../src/airflow/providers/common/sql/hooks/sql.py  |  35 ++
 .../src/airflow/providers/common/sql/hooks/sql.pyi |   4 +
 .../sql/tests/unit/common/sql/hooks/test_sql.py    |  60 +++
 14 files changed, 1303 insertions(+), 42 deletions(-)

diff --git a/providers/common/ai/docs/agent_security.rst 
b/providers/common/ai/docs/agent_security.rst
index 9ce4a350a7f..9fe73ac7779 100644
--- a/providers/common/ai/docs/agent_security.rst
+++ b/providers/common/ai/docs/agent_security.rst
@@ -186,7 +186,10 @@ How ``allowed_tables`` is enforced
 
 When ``allowed_tables`` is set it governs every tool, not just discovery:
 
-- ``list_tables`` and ``get_schema`` only reveal listed tables.
+- ``list_tables``, ``get_schema`` and, when a ``search_backend`` is set, 
``search_tables``
+  only reveal listed tables. With ``allowed_tables`` unset, ``search_tables`` 
results are not filtered,
+  so a standalone backend indexed with a broader role than the agent's 
connection can leak
+  table names and comments; see :ref:`the identity warning 
<sql-toolset-search-identity>`.
 - ``query`` and ``check_query`` parse the SQL with `sqlglot
   <https://github.com/tobymao/sqlglot>`_ and reject it before execution if it
   references any table that is not on the list. Tables reached indirectly are
diff --git a/providers/common/ai/docs/toolsets/progressive_disclosure.rst 
b/providers/common/ai/docs/toolsets/progressive_disclosure.rst
index c27c77ddac0..0978bf88199 100644
--- a/providers/common/ai/docs/toolsets/progressive_disclosure.rst
+++ b/providers/common/ai/docs/toolsets/progressive_disclosure.rst
@@ -138,7 +138,8 @@ When it does not help
 answer almost anything, so hiding them saves a few definitions and adds a 
search request
 to nearly every run. On a warehouse with hundreds of tables, ``list_tables``
 output grows with the table count; limit it with ``allowed_tables`` (see
-:ref:`sql-toolset-restricted`).
+:ref:`sql-toolset-restricted`), or pass a ``search_backend`` so the agent 
finds tables with
+``search_tables`` instead of reading the whole list (see 
:ref:`sql-toolset-table-search`).
 
 ``AgentSkillsToolset`` has four tools, and its list of skills goes into the 
agent's
 instructions, which ``.defer_loading()`` does not touch, so the part of its 
cost that grows
diff --git a/providers/common/ai/docs/toolsets/sql.rst 
b/providers/common/ai/docs/toolsets/sql.rst
index 914451fb2d2..0ba86a6ac03 100644
--- a/providers/common/ai/docs/toolsets/sql.rst
+++ b/providers/common/ai/docs/toolsets/sql.rst
@@ -19,7 +19,8 @@ SQL databases: ``SQLToolset``
 =============================
 
 Curated toolset wrapping
-:class:`~airflow.providers.common.sql.hooks.sql.DbApiHook` with four tools:
+:class:`~airflow.providers.common.sql.hooks.sql.DbApiHook` with four tools, 
and a fifth,
+``search_tables``, when you pass a ``search_backend``:
 
 .. list-table::
    :header-rows: 1
@@ -28,10 +29,16 @@ Curated toolset wrapping
    * - Tool
      - Description
    * - ``list_tables``
-     - Lists available table names (filtered by ``allowed_tables`` if set)
+     - Lists available table names (filtered by ``allowed_tables`` if set). 
With a
+       ``search_backend``, returns only a count once there are more than
+       ``max_listed_tables`` (see :ref:`sql-toolset-table-search`)
    * - ``get_schema``
-     - Returns a table's columns as JSON, with a ``name_contains`` filter and a
-       bounded summary on very wide tables (see :ref:`bounded-schema-results`)
+     - Returns a table's columns as JSON, with table and column comments where 
the
+       database has them, a ``name_contains`` filter and a bounded summary on 
very
+       wide tables (see :ref:`bounded-schema-results`)
+   * - ``search_tables``
+     - Only with ``search_backend``. Finds tables by a free-text query over 
table names
+       and comments (see :ref:`sql-toolset-table-search`)
    * - ``query``
      - Executes a SQL query and returns bounded, columnar JSON (see
        :ref:`bounded-query-results`)
@@ -337,9 +344,223 @@ Parameters
   Must be a valid Python identifier. Set it when one agent has another toolset 
with the
   same tool names, since duplicate tool names are rejected. A function passed 
to
   ``.approval_required()`` sees these prefixed names; see ``allow_writes`` 
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+  ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment`` 
is
+  present only for tables that have one. Default ``False``. ``get_schema`` 
returns
+  comments regardless. Without a ``search_backend`` the list has no size 
limit: each
+  comment is capped at 200 characters, but the total is not bound by 
``max_result_bytes``.
+  See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``, 
no search
+  tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above 
which
+  ``list_tables`` stops listing and points at ``search_tables``. Default 
``100``. No
+  effect without a ``search_backend``.
 - ``max_retries``: How many times the model may correct a failed call to these
   tools. Default ``None``, the agent's ``retries``. See 
:ref:`toolset-retry-budget`.
 
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database 
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on 
each column
+where the database reports one. This is what it returned for a ``customers`` 
table with
+comments, on PostgreSQL (the ``email`` and ``country_code`` columns have 
comments, the
+others do not):
+
+.. code-block:: json
+
+    
{"columns":[{"name":"customer_id","type":"INTEGER"},{"name":"email","type":"TEXT","comment":"Login
 address, unique per 
customer"},{"name":"full_name","type":"TEXT"},{"name":"created_at","type":"TIMESTAMP"},{"name":"country_code","type":"CHAR(2)","comment":"ISO
 3166-1 alpha-2"}],"column_count":5,"table_comment":"People who have an account 
in the shop."}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``. On a dialect that cannot report 
comments
+(SQLite, for one), ``get_schema`` works as before and returns none. A failure 
reading the
+columns fails the call. A failure reading the *table* comment, of any kind, a 
permission
+error included, is logged at debug level and ``get_schema`` returns the 
columns without
+``table_comment``.
+
+Each comment is cut to 200 characters. Comments share ``max_result_bytes`` 
with the
+columns, so a wide table with long comments can cross the budget where the 
same table
+without them would not. When the columns fit but their comments do not, 
``get_schema``
+returns the full column list without the column comments and says so with 
``comments_omitted``.
+Only when the columns alone do not fit is the result summarized (see
+:ref:`bounded-schema-results`), and the summary leaves the column comments out 
too. In both
+cases the agent gets the comments back by asking for the columns it needs with 
``name_contains``.
+
+``include_table_comments=True`` adds the table comments to ``list_tables``, 
and the default
+search backend reads the same comments to build its catalog. Both call the 
Inspector's
+``get_multi_table_comment`` once per schema. When ``allowed_tables`` is set, 
only the allowed
+tables' comments are requested. What that costs depends on the dialect. Where 
the dialect does
+not implement ``get_multi_table_comment`` itself, SQLAlchemy's default reads 
the comments one
+table at a time.
+
+On PostgreSQL 14 with SQLAlchemy 2.0, one ``list_tables`` call with
+``include_table_comments=True`` on a schema of ten tables issued two SQL 
statements, counted
+with SQLAlchemy's ``before_cursor_execute`` event: one listing the table names 
and one
+reading the comments of all the tables. Snowflake and BigQuery were not run. 
Reading their
+SQLAlchemy dialects' source suggests they do not implement 
``get_multi_table_comment``, so
+there each table whose comment is read would cost one query, or one API call 
on BigQuery, and a
+schema with many tables would make this slow. On such databases leave
+``include_table_comments`` off for large schemas, or pass a custom 
``search_backend`` that
+builds its catalog with one dialect-specific query, for example against
+``INFORMATION_SCHEMA`` (see :ref:`sql-toolset-custom-search-backend`). If the 
comments cannot
+be read, for example because the role cannot see the comment catalog, tables 
are returned
+without them. A lost connection is still reported as a connection error. The 
catalog the
+search backend reads is built once per toolset instance (see
+:ref:`sql-toolset-table-search`), so the default search backend will not look 
for comments
+again.
+
+SQLAlchemy 1.4 has no ``get_multi_table_comment``, so there ``list_tables`` 
returns tables without
+comments rather than reading them table by table. The default search backend 
then has no comments
+to match, and a query whose words appear only in comments returns nothing; 
matching on table
+names still works.
+
+.. _sql-toolset-table-search:
+
+Finding tables in a large warehouse
+-----------------------------------
+
+Listing every table stops working once there are hundreds. Pass a 
``search_backend`` and the
+agent gets a ``search_tables`` tool that takes a free-text ``query`` and an 
optional
+``limit`` (default 10, at most 50) and returns matching tables with their 
comments. Once
+there are more than ``max_listed_tables`` tables, ``list_tables`` returns only 
their number
+and a pointer to ``search_tables`` instead of the whole list. Without a 
``search_backend``
+``list_tables`` is unchanged, however many tables there are.
+
+.. code-block:: python
+
+    from airflow.providers.common.ai.toolsets.sql import SQLToolset
+    from airflow.providers.common.ai.utils.sql_search import 
KeywordTableSearchBackend
+
+    toolset = SQLToolset(
+        db_conn_id="warehouse",
+        schema="analytics",
+        search_backend=KeywordTableSearchBackend(),
+    )
+
+The outputs below come from a run on PostgreSQL with ten tables. That run also 
set
+``include_table_comments=True`` and ``max_listed_tables=5``, which the block 
above leaves at
+their defaults (``False`` and 100), so with the block as written 
``list_tables`` would list all
+ten tables. With those two arguments set, ``list_tables`` returned:
+
+.. code-block:: json
+
+    {"table_count": 10, "hint": "There are 10 tables, too many to list (the 
limit is 5). Use search_tables to find the ones you need."}
+
+and ``search_tables`` with the query ``parcels carrier`` returned:
+
+.. code-block:: json
+
+    {"tables": [{"name": "shipments", "comment": "Parcels handed to a carrier 
for an order."}]}
+
+A query that matches nothing returns ``{"tables": []}``. Comments are cut to 
200
+characters in these results, the same as in ``list_tables``.
+
+``KeywordTableSearchBackend`` splits the query on whitespace and matches each 
word,
+case-insensitively, as a substring of the table name and of the table's 
comment. For a
+``SCHEMA.TABLE`` name only the table part is matched, not the schema. Column 
comments are
+not searched, and a word is not stemmed: a query for ``returned`` does not 
match a table
+named ``returns``. A word found in the name counts three times a word found in 
the comment;
+tables matching nothing are dropped and ties are ordered by name.
+
+The toolset builds the table catalog the first time a backend asks for it and 
keeps it for
+the lifetime of the toolset instance. This holds for any backend that calls 
``get_catalog``,
+the default one included, so a table created after the first search does not 
show up until
+the toolset is built again.
+
+When ``allowed_tables`` is set, every result is checked against it before the 
agent sees it,
+whichever backend produced it, so a backend that knows about more tables than 
the agent may
+use cannot widen access. When ``allowed_tables`` is unset (the default, 
allow-all), the
+toolset filters nothing: a backend indexed with a broader role than the 
agent's connection can
+show the agent table names and comments that connection cannot see. In that 
case the
+:ref:`warning below <sql-toolset-search-identity>` is the only control.
+
+.. _sql-toolset-search-identity:
+
+.. warning::
+
+    Introspection must run as the same identity the agent queries with, because
+    ``INFORMATION_SCHEMA`` and the equivalent catalogs show a role only what 
that role can see.
+    The default path is correct by construction: the toolset introspects 
through the same
+    ``db_conn_id`` it queries with. A standalone backend, an external vector 
store for
+    instance, is responsible for building its index with that identity or 
equivalent
+    privileges. Otherwise it can surface tables the agent cannot use, or miss 
tables it can.
+
+.. _sql-toolset-custom-search-backend:
+
+Plugging in another backend
+^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+A backend is any object with a ``search_tables(query, limit, *, get_catalog)`` 
method
+returning ``(name, comment)`` pairs, best match first (see
+:class:`~airflow.providers.common.ai.utils.sql_search.SQLTableSearchBackend`). 
Names
+must be in the form ``list_tables`` returns -- bare for the default schema,
+``SCHEMA.TABLE`` otherwise. A backend with its own index, such as a vector 
store, a search
+service or a retriever over table descriptions, ignores ``get_catalog`` and 
queries that
+index. This provider ships no such backend; integration code is yours to write 
and test
+against your own store.
+
+``get_catalog`` is how the default backend reads comments, through 
SQLAlchemy's reflection; on a
+dialect without ``get_multi_table_comment`` that costs one query per table. A 
backend that avoids
+it has to reach the database itself. This example resolves a hook from the 
same ``db_conn_id``
+the toolset queries with and runs one query against the PostgreSQL catalogs. 
The query reads
+PostgreSQL's ``pg_class`` and ``pg_description``, the catalogs SQLAlchemy's 
PostgreSQL dialect
+reads table comments from; adapt it to the catalog of your database:
+
+.. code-block:: python
+
+    from collections.abc import Callable, Sequence
+
+    from airflow.providers.common.ai.toolsets.sql import SQLToolset
+    from airflow.providers.common.ai.utils.sql_search import TableEntry
+    from airflow.providers.common.compat.sdk import BaseHook
+
+
+    class PostgresCatalogSearchBackend:
+        def __init__(self, db_conn_id: str, schema: str) -> None:
+            self._db_conn_id = db_conn_id
+            self._schema = schema
+
+        def search_tables(
+            self, query: str, limit: int, *, get_catalog: Callable[[], 
Sequence[TableEntry]]
+        ) -> Sequence[TableEntry]:
+            hook = BaseHook.get_connection(self._db_conn_id).get_hook()
+            patterns = [f"%{word}%" for word in query.casefold().split()]
+            if not patterns:
+                return []
+            rows = hook.get_records(
+                "SELECT c.relname, d.description "
+                "FROM pg_catalog.pg_class c "
+                "JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
+                "LEFT JOIN pg_catalog.pg_description d "
+                "ON d.objoid = c.oid AND d.objsubid = 0 "
+                "AND d.classoid = 'pg_catalog.pg_class'::regclass "
+                "WHERE n.nspname = %s AND c.relkind IN ('r', 'p', 'f') "
+                "AND (lower(c.relname) LIKE ANY (%s) OR lower(d.description) 
LIKE ANY (%s)) "
+                "ORDER BY c.relname LIMIT %s",
+                parameters=(self._schema, patterns, patterns, limit),
+            )
+            return [(name, comment) for name, comment in rows]
+
+
+    toolset = SQLToolset(
+        db_conn_id="warehouse",
+        schema="analytics",
+        search_backend=PostgresCatalogSearchBackend("warehouse", "analytics"),
+    )
+
+This backend splits the query into words like the default one, but it is not 
equivalent to it.
+``%`` and ``_`` in the agent's query act as ``LIKE`` wildcards (the values are 
bound, so this
+is not injection), it does not weigh a name match above a comment match, and 
it orders by table
+name.
+
+When ``allowed_tables`` is set, the toolset checks what such a backend returns 
against it
+before the agent sees it, the same as for the default backend. With 
``allowed_tables`` unset
+nothing is filtered, so the warning above applies: the backend queries with 
whatever identity
+its hook's connection has. Comments the backend returns are masked for 
registered secrets
+and cut to 200 characters before the agent sees them.
+
 .. _bounded-query-results:
 
 Bounded query results
@@ -422,7 +643,8 @@ summary that reports the shape and points at the filter:
 ``truncated_by`` is ``max_columns`` or ``max_result_bytes``. 
``type_histogram`` counts
 columns per type (capped to the most common, the tail folded into one entry), 
and
 ``sample_columns`` previews the first columns -- both only while they fit the 
budget, so
-a pathologically small budget still returns the ``column_count`` and ``hint``. 
A filtered
+a pathologically small budget still returns the ``column_count`` and ``hint``. 
Column comments count
+against the same budget (see :ref:`sql-toolset-comments`). A filtered
 call echoes ``name_contains`` and adds ``total_columns`` so a subset is never 
mistaken for
 the whole table.
 
diff --git a/providers/common/ai/pyproject.toml 
b/providers/common/ai/pyproject.toml
index 63c0ecde342..19eefd0cfd1 100644
--- a/providers/common/ai/pyproject.toml
+++ b/providers/common/ai/pyproject.toml
@@ -157,11 +157,11 @@ dependencies = [
     "pyarrow>=22.0.0; python_version >= '3.14'",
 ]
 "sql" = [
-    "apache-airflow-providers-common-sql>=2.2.0",
+    "apache-airflow-providers-common-sql>=2.2.0",  # use next version
     "sqlglot>=30.0.0",
 ]
 "common.sql" = [
-    "apache-airflow-providers-common-sql>=2.2.0"
+    "apache-airflow-providers-common-sql>=2.2.0"  # use next version
 ]
 "langchain" = [
     "langchain>=1.0.0",
diff --git 
a/providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py 
b/providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py
index 4c647276d24..b3918566cb6 100644
--- a/providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py
+++ b/providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py
@@ -18,6 +18,7 @@
 
 from __future__ import annotations
 
+import logging
 from contextlib import suppress
 from typing import TYPE_CHECKING, Any
 
@@ -43,7 +44,7 @@ from pydantic_ai.tools import ToolDefinition
 from pydantic_ai.toolsets.abstract import ToolsetTool
 
 from airflow.providers.common.ai.exceptions import SQLToolsetConnectionError
-from airflow.providers.common.ai.utils.masking import dumps_masked
+from airflow.providers.common.ai.utils.masking import dumps_masked, 
mask_secrets
 from airflow.providers.common.ai.utils.query_results import (
     DEFAULT_MAX_COLUMNS,
     DEFAULT_MAX_RESULT_BYTES,
@@ -51,6 +52,7 @@ from airflow.providers.common.ai.utils.query_results import (
     build_get_schema_description,
     build_query_result,
     build_schema_result,
+    truncate_comment,
 )
 from airflow.providers.common.ai.utils.tool_definition import 
build_args_validator, return_schema_kwargs
 from airflow.providers.common.ai.utils.tool_prefix import ToolPrefixMixin
@@ -63,6 +65,10 @@ if TYPE_CHECKING:
     from pydantic_ai._run_context import RunContext
     from sqlalchemy.engine.reflection import Inspector
 
+    from airflow.providers.common.ai.utils.sql_search import 
SQLTableSearchBackend, TableEntry
+
+
+log = logging.getLogger(__name__)
 
 # Sentinel distinguishing "caller did not pass ``allowed_tables``" (expose 
every
 # table) from an explicit value. Every explicit falsy value -- ``None`` as 
much as
@@ -92,6 +98,26 @@ _GET_SCHEMA_SCHEMA: dict[str, Any] = {
     "required": ["table_name"],
 }
 
+_DEFAULT_SEARCH_LIMIT = 10
+_MAX_SEARCH_LIMIT = 50
+
+_SEARCH_TABLES_SCHEMA: dict[str, Any] = {
+    "type": "object",
+    "properties": {
+        "query": {
+            "type": "string",
+            "description": "Words describing the data you are looking for, 
matched against table names and comments.",
+        },
+        "limit": {
+            "type": "integer",
+            "description": "Maximum number of tables to return (default 10, at 
most 50).",
+            "minimum": 1,
+            "maximum": 50,
+        },
+    },
+    "required": ["query"],
+}
+
 _QUERY_SCHEMA: dict[str, Any] = {
     "type": "object",
     "properties": {
@@ -307,6 +333,21 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
     :param max_retries: How many times the model may correct a failed call to 
one of these
         tools before the run fails. ``None`` (the default) uses the agent's 
tool retry
         budget, its ``retries``, as pydantic-ai's own toolsets do.
+    :param include_table_comments: When ``True``, ``list_tables`` returns
+        ``{"name": ..., "comment": ...}`` objects instead of bare names, with 
``comment``
+        present only for tables that have one (each capped to 200 characters). 
Without a
+        ``search_backend`` the list has no size limit: the total is not bound 
by
+        ``max_result_bytes``. Default ``False`` keeps the list of names. 
``get_schema`` always
+        returns table and column comments where the database reports them, 
regardless of this flag.
+    :param search_backend: Registers a ``search_tables`` tool that finds 
tables by a free-text
+        query, for databases with too many tables to list. Pass
+        
:class:`~airflow.providers.common.ai.utils.sql_search.KeywordTableSearchBackend`
 for a
+        keyword match over table names and comments, or any object implementing
+        
:class:`~airflow.providers.common.ai.utils.sql_search.SQLTableSearchBackend`. 
Whatever it
+        returns is filtered through ``allowed_tables``. ``None`` (the default) 
registers no search tool.
+    :param max_listed_tables: With a ``search_backend``, ``list_tables`` 
returns only the number of
+        tables and a pointer to ``search_tables`` once there are more than 
this many. Default ``100``.
+        Without a ``search_backend`` there is no limit.
     """
 
     # Rendered, on a copy, by AgentOperator. Deliberately not 
``template_fields``, which
@@ -326,6 +367,9 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
         max_columns: int = DEFAULT_MAX_COLUMNS,
         tool_prefix: str = "",
         max_retries: int | None = None,
+        include_table_comments: bool = False,
+        search_backend: SQLTableSearchBackend | None = None,
+        max_listed_tables: int = 100,
     ) -> None:
         self._max_retries = validate_max_retries(max_retries)
         self._set_tool_prefix(tool_prefix)
@@ -352,6 +396,10 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
         self._max_rows = max_rows
         self._max_result_bytes = max_result_bytes
         self._max_columns = max_columns
+        self._include_table_comments = include_table_comments
+        self._search_backend = search_backend
+        self._max_listed_tables = max_listed_tables
+        self._catalog: list[TableEntry] | None = None
         self._hook: DbApiHook | None = None
 
         # Canonical ``(catalog, schema, table)`` view of allowed_tables for 
membership
@@ -449,16 +497,30 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
         max_retries = self._get_tool_max_retries(ctx)
         tools: dict[str, ToolsetTool[Any]] = {}
 
-        for base, description, schema in (
+        definitions = [
             ("list_tables", "List available table names in the database.", 
_LIST_TABLES_SCHEMA),
             (
                 "get_schema",
-                
build_get_schema_description(tool_name=self._build_tool_name("get_schema")),
+                build_get_schema_description(
+                    tool_name=self._build_tool_name("get_schema"), 
include_comments=True
+                ),
                 _GET_SCHEMA_SCHEMA,
             ),
             ("query", _QUERY_DESCRIPTION, _QUERY_SCHEMA),
             ("check_query", "Validate SQL syntax without executing it.", 
_CHECK_QUERY_SCHEMA),
-        ):
+        ]
+        if self._search_backend is not None:
+            definitions.append(
+                (
+                    "search_tables",
+                    "Find tables by words describing the data you need, 
matched against table names "
+                    "and comments. Returns JSON of the form "
+                    '{"tables": [{"name": ..., "comment": ...}, ...]}; pass a 
returned name to '
+                    f"{self._build_tool_name('get_schema')}.",
+                    _SEARCH_TABLES_SCHEMA,
+                )
+            )
+        for base, description, schema in definitions:
             # sequential=True keeps pydantic-ai from running these calls 
concurrently
             # within a turn; run_blocking's process-wide lock serializes them 
with the
             # blocking calls of the other toolsets that use it.
@@ -489,7 +551,7 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
         tool: ToolsetTool[Any],
     ) -> Any:
         base = self._find_base_tool_name(name)
-        if base not in ("list_tables", "get_schema", "query", "check_query"):
+        if base not in self._get_tool_names():
             raise ValueError(f"Unknown tool: {name!r}")
         try:
             return await self.run_blocking(self._run_tool, base, tool_args)
@@ -504,14 +566,23 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
             # can fix its SQL within the run, bounded by the tool's 
max_retries.
             raise ModelRetry(
                 f"The {name} tool failed: {e}\n"
-                f"Use the {self._build_tool_name('list_tables')} and 
{self._build_tool_name('get_schema')} tools "
-                "to inspect the database, "
-                "then fix the query and try again."
+                f"Use the {' and '.join(self._build_tool_name(n) for n in 
self._get_discovery_tool_names())} tools "
+                "to inspect the database, then fix the query and try again."
             ) from e
 
+    def _get_tool_names(self) -> tuple[str, ...]:
+        names = ("list_tables", "get_schema", "query", "check_query")
+        return (*names, "search_tables") if self._search_backend is not None 
else names
+
+    def _get_discovery_tool_names(self) -> tuple[str, ...]:
+        names = ("list_tables", "get_schema")
+        return (*names, "search_tables") if self._search_backend is not None 
else names
+
     def _run_tool(self, name: str, tool_args: dict[str, Any]) -> str:
         if name == "list_tables":
             return self._list_tables()
+        if name == "search_tables":
+            return self._search_tables(tool_args["query"], 
tool_args.get("limit"))
         if name == "get_schema":
             return self._get_schema(tool_args["table_name"], 
tool_args.get("name_contains"))
         if name == "query":
@@ -529,9 +600,10 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
             return self._schema, table_name
         return schema, table
 
-    def _list_tables(self) -> str:
+    def _find_allowed_tables(self) -> list[tuple[str | None, str, str]]:
+        """Introspect the exposed schemas; return ``(schema, name, display 
name)`` per allowed table."""
         inspector = self._get_inspector()
-        tables: list[str] = []
+        tables: list[tuple[str | None, str, str]] = []
         # Dedupe by (schema, table) so a table reachable both qualified and 
via the
         # default schema (e.g. "public.users" and "users" with 
schema="public") is
         # listed once. Case-folded because databases reflect identifiers in 
their case.
@@ -541,7 +613,7 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
             key = self._canonical_ref("", schema, name)
             if self._is_ref_allowed("", schema, name) and key not in seen:
                 seen.add(key)
-                tables.append(display)
+                tables.append((schema, name, display))
 
         # Schemas referenced by qualified allowed_tables entries: introspect 
each
         # and return matching tables fully qualified so they round-trip to 
get_schema.
@@ -554,23 +626,122 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
         if self._include_default_schema:
             for name in inspector.get_table_names(schema=self._schema):
                 add(self._schema, name, name)
+        return tables
+
+    def _list_tables(self) -> str:
+        tables = self._find_allowed_tables()
+        if self._search_backend is not None and len(tables) > 
self._max_listed_tables:
+            return dumps_masked(
+                {
+                    "table_count": len(tables),
+                    "hint": (
+                        f"There are {len(tables)} tables, too many to list 
(the limit is "
+                        f"{self._max_listed_tables}). Use 
{self._build_tool_name('search_tables')} to find the ones you need."
+                    ),
+                }
+            )
+        if not self._include_table_comments:
+            return dumps_masked([display for _, _, display in tables])
+        return dumps_masked(
+            [
+                {"name": name, **({"comment": truncate_comment(comment)} if 
comment else {})}
+                for name, comment in self._get_table_entries(tables)
+            ]
+        )
+
+    def _get_table_entries(self, tables: list[tuple[str | None, str, str]]) -> 
list[TableEntry]:
+        """Pair each table's display name with its full comment, registered 
secrets masked, read once per schema."""
+        inspector = self._get_inspector()
+        names_by_schema: dict[str | None, list[str]] = {}
+        for table_schema, name, _ in tables:
+            names_by_schema.setdefault(table_schema, []).append(name)
+        # Names come from `tables` (as reflected), not from allowed_tables: 
SQLAlchemy's
+        # filter_names match is case-sensitive while allowed_tables matching 
is not.
+        comments = {
+            table_schema: self._get_table_comments(
+                inspector, table_schema, None if self._allowed_canonical is 
None else names
+            )
+            for table_schema, names in names_by_schema.items()
+        }
+        # Full text so search sees the whole comment; masked because the 
catalog is handed to `search_backend`.
+        return [
+            (display, mask_secrets(comment) if (comment := 
comments[table_schema].get(name)) else None)
+            for table_schema, name, display in tables
+        ]
+
+    def _get_catalog(self) -> list[TableEntry]:
+        """Return the exposed tables with comments, introspected on first use 
and kept for this toolset."""
+        if self._catalog is None:
+            self._catalog = 
self._get_table_entries(self._find_allowed_tables())
+        return self._catalog
+
+    def _search_tables(self, query: str, limit: int | None) -> str:
+        if self._search_backend is None:
+            raise ValueError("search_tables requires a search_backend")
+        # The schema's maximum is only advice to the model; the cap has to 
hold here.
+        limit = min(max(_DEFAULT_SEARCH_LIMIT if limit is None else limit, 1), 
_MAX_SEARCH_LIMIT)
+        found = self._search_backend.search_tables(query, limit, 
get_catalog=self._get_catalog)
+        tables: list[dict[str, str]] = []
+        for display, comment in found:
+            schema, table = self._split_table_identifier(display)
+            if not self._is_ref_allowed("", schema, table):
+                continue
+            tables.append({"name": display, **({"comment": 
truncate_comment(comment)} if comment else {})})
+            if len(tables) == limit:
+                break
+        return dumps_masked({"tables": tables})
+
+    @staticmethod
+    def _get_table_comments(
+        inspector: Inspector, schema: str | None, table_names: Sequence[str] | 
None
+    ) -> dict[str, str]:
+        """
+        Return ``{table name: comment}`` for a schema in one call; empty when 
the dialect has none.
 
-        return dumps_masked(tables)
+        ``table_names`` limits the read to those tables; ``None`` reads the 
whole schema.
+        """
+        # SQLAlchemy 1.4, which common-sql still supports, has no 
get_multi_table_comment.
+        # Reading comments one table at a time instead would cost a query per 
table, so
+        # on 1.4 the tables simply come back without comments.
+        if (get_comments := getattr(inspector, "get_multi_table_comment", 
None)) is None:
+            return {}
+        try:
+            reflected = get_comments(schema=schema, filter_names=table_names)
+        except NotImplementedError:
+            return {}
+        except Exception as e:
+            # A lost connection is not degraded: the result is cached by the 
search catalog.
+            if _is_lost_connection(e):
+                raise
+            # Unreadable comments (a role without access to the comment 
catalog, a driver
+            # error) must not take the table names down with them.
+            log.debug("Could not read the table comments of schema %s", 
schema, exc_info=True)
+            return {}
+        return {name: text for (_, name), info in reflected.items() if (text 
:= info.get("text"))}
 
     def _get_schema(self, table_name: str, name_contains: str | None = None) 
-> str:
         schema, table = self._split_table_identifier(table_name)
         if not self._is_ref_allowed("", schema, table):
             return dumps_masked({"error": f"Table {table_name!r} is not in the 
allowed tables list."})
-        columns = [
-            {"name": col["name"], "type": str(col["type"])}
-            for col in self._get_inspector().get_columns(table, schema=schema)
-        ]
+        # Called only for its side effect: SQLAlchemy setup failures surface 
here as
+        # SQLToolsetConnectionError; dropping it lets them escape the hook as 
plain errors.
+        self._get_inspector()
+        hook = self._get_db_hook()
+        columns = hook.get_table_columns_with_comments(table, schema=schema)
+        table_comment: str | None = None
+        # Broad on purpose: a hook-level failure reading only the comment must 
not
+        # take get_schema down when the columns were read fine.
+        try:
+            table_comment = hook.get_table_comment(table, schema=schema)
+        except Exception:
+            log.debug("Could not read the comment of table %s", table, 
exc_info=True)
         return build_schema_result(
             columns,
             max_columns=self._max_columns,
             max_result_bytes=self._max_result_bytes,
             name_contains=name_contains,
             tool_name=self._build_tool_name("get_schema"),
+            table_comment=table_comment,
         )
 
     def _dialect_for_validation(self) -> str | None:
diff --git 
a/providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py 
b/providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py
index eff89771fdc..4ce0fad2aa8 100644
--- a/providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py
+++ b/providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py
@@ -40,7 +40,7 @@ from collections import Counter
 from collections.abc import Sequence
 from typing import Any
 
-from airflow.providers.common.ai.utils.masking import dumps_masked
+from airflow.providers.common.ai.utils.masking import dumps_masked, 
mask_secrets
 
 # A policy default, not a limit imposed by any storage, protocol, or model 
layer:
 # roughly 16k tokens at 4 characters per token. Large enough that ordinary 
queries are
@@ -64,6 +64,10 @@ _SCHEMA_TYPE_HISTOGRAM_OTHER_KEY = "(other)"
 # "here is what the names look like, now filter", never as a usable prefix of 
the table.
 _SCHEMA_SAMPLE_SIZE = 20
 
+# Longest table or column comment returned, in characters. Comments are free 
text written by
+# people, so one pasted paragraph must not be able to dominate the result.
+DEFAULT_MAX_COMMENT_CHARS = 200
+
 # Tool results are machine-read, so no whitespace. ensure_ascii=False matters 
as much as
 # the separators: escaping one CJK character to \uXXXX costs six bytes instead 
of three,
 # so an ASCII-escaped result is charged several times over against the budget 
and
@@ -98,16 +102,40 @@ _GET_SCHEMA_DESCRIPTION_TEMPLATE = (
     "table."
 )
 
+# Appended only for toolsets whose results can carry comments, so the 
description never promises
+# keys a toolset does not return.
+_GET_SCHEMA_COMMENTS_DESCRIPTION = (
+    " Each column carries a `comment`, and the result a `table_comment`, when 
the database provides "
+    "one; `comments_omitted` means the column comments were left out to fit."
+)
+
+
+def build_get_schema_description(*, tool_name: str = "get_schema", 
include_comments: bool = False) -> str:
+    """
+    Render the ``get_schema`` tool description naming the tool as the model 
sees it.
 
-def build_get_schema_description(*, tool_name: str = "get_schema") -> str:
-    """Render the ``get_schema`` tool description naming the tool as the model 
sees it."""
-    return _GET_SCHEMA_DESCRIPTION_TEMPLATE.format(tool_name=tool_name)
+    :param tool_name: The tool name as the model sees it, including any prefix.
+    :param include_comments: Describe the comment keys; set only for toolsets 
that return comments.
+    """
+    description = _GET_SCHEMA_DESCRIPTION_TEMPLATE.format(tool_name=tool_name)
+    if include_comments:
+        description += _GET_SCHEMA_COMMENTS_DESCRIPTION
+    return description
 
 
 def _dumps(payload: Any) -> str:
     return dumps_masked(payload, **_DUMP_KWARGS)
 
 
+def truncate_comment(comment: str, *, max_chars: int = 
DEFAULT_MAX_COMMENT_CHARS) -> str:
+    """Mask registered secrets in ``comment``, then cap it to ``max_chars`` 
characters, marking a cut with a trailing ellipsis."""
+    # Mask before cutting: a cut secret is a prefix that no longer equals the 
registered value, so the masker cannot find it.
+    comment = mask_secrets(comment)
+    if len(comment) <= max_chars:
+        return comment
+    return comment[: max_chars - 1] + "\u2026"
+
+
 def _size(payload: Any) -> int:
     """Measure the serialized size in bytes -- not characters, which diverge 
outside ASCII."""
     return len(_dumps(payload).encode("utf-8"))
@@ -228,6 +256,7 @@ def build_schema_result(
     max_result_bytes: int,
     name_contains: str | None = None,
     tool_name: str = "get_schema",
+    table_comment: str | None = None,
 ) -> str:
     """
     Render a table's columns as a bounded JSON tool result.
@@ -238,7 +267,13 @@ def build_schema_result(
     the way to retrieve specific columns. ``columns`` is assumed to hold 
distinct names (a table's
     introspected columns are unique by construction).
 
-    :param columns: ``{"name", "type"}`` dicts in table order.
+    Comments count against the same budget as the columns: each one is capped 
to
+    ``DEFAULT_MAX_COMMENT_CHARS`` characters, and when the result does not 
fit, the column comments
+    are dropped first (``comments_omitted`` says so) and the full column list 
is returned. Only if
+    the columns alone still do not fit does the summary replace them; it 
likewise carries no column
+    comments.
+
+    :param columns: ``{"name", "type"}`` dicts in table order, each optionally 
with a ``comment``.
     :param max_columns: Column count above which a summary replaces the full 
list.
     :param max_result_bytes: Budget for the serialized result.
     :param name_contains: Case-insensitive substring. When given (and 
non-empty), only matching
@@ -246,8 +281,12 @@ def build_schema_result(
         for the whole table.
     :param tool_name: Name of the ``get_schema`` tool as the model sees it 
(including any
         ``tool_prefix``), so hints point at this toolset's own tool.
+    :param table_comment: The table's comment, returned as ``table_comment`` 
when non-empty.
     """
     name_contains = name_contains or None
+    columns = [
+        {**col, "comment": truncate_comment(col["comment"])} if "comment" in 
col else col for col in columns
+    ]
     total_columns = len(columns)
     if name_contains is not None:
         needle = name_contains.casefold()
@@ -280,11 +319,25 @@ def build_schema_result(
         )
 
     full: dict[str, Any] = {"columns": selected, "column_count": len(selected)}
+    if table_comment:
+        full["table_comment"] = truncate_comment(table_comment)
     if name_contains is not None:
         full["name_contains"] = name_contains
         full["total_columns"] = total_columns
-    if len(selected) <= max_columns and _size(full) <= max_result_bytes:
-        return _dumps(full)
+    if len(selected) <= max_columns:
+        if _size(full) <= max_result_bytes:
+            return _dumps(full)
+        if any("comment" in col for col in selected):
+            without_comments = {
+                **full,
+                "columns": _drop_column_comments(selected),
+                "comments_omitted": True,
+                "hint": _build_comments_omitted_hint(
+                    max_result_bytes=max_result_bytes, 
name_contains=name_contains, tool_name=tool_name
+                ),
+            }
+            if _size(without_comments) <= max_result_bytes:
+                return _dumps(without_comments)
 
     return _summarize_schema(
         selected,
@@ -293,9 +346,21 @@ def build_schema_result(
         max_result_bytes=max_result_bytes,
         name_contains=name_contains,
         tool_name=tool_name,
+        table_comment=full.get("table_comment"),
     )
 
 
+def _drop_column_comments(columns: Sequence[dict[str, str]]) -> list[dict[str, 
str]]:
+    return [{k: v for k, v in col.items() if k != "comment"} for col in 
columns]
+
+
+def _build_comments_omitted_hint(*, max_result_bytes: int, name_contains: str 
| None, tool_name: str) -> str:
+    reason = f"Column comments were left out to fit max_result_bytes 
({max_result_bytes})."
+    if name_contains is not None:
+        return f"{reason} Use a more specific name_contains substring to get 
their comments."
+    return f"{reason} Call {tool_name} with a name_contains substring to get 
the comments of the columns you need."
+
+
 def _summarize_schema(
     selected: list[dict[str, str]],
     *,
@@ -304,6 +369,7 @@ def _summarize_schema(
     max_result_bytes: int,
     name_contains: str | None,
     tool_name: str,
+    table_comment: str | None,
 ) -> str:
     """Build the bounded summary returned when the full column list does not 
fit."""
     # Count is checked before bytes: it is the cheaper, more explainable 
bound. A bytes-only
@@ -328,6 +394,12 @@ def _summarize_schema(
         "truncated_by": truncated_by,
         "hint": f"{reason} {move}",
     }
+    if table_comment:
+        output["table_comment"] = table_comment
+    if any("comment" in col for col in selected):
+        output["comments_omitted"] = True
+        output["hint"] += " Column comments are omitted from this summary and 
are returned with the columns."
+        selected = _drop_column_comments(selected)
     if name_contains is not None:
         output["name_contains"] = name_contains
         output["total_columns"] = total_columns
diff --git 
a/providers/common/ai/src/airflow/providers/common/ai/utils/sql_search.py 
b/providers/common/ai/src/airflow/providers/common/ai/utils/sql_search.py
new file mode 100644
index 00000000000..9cdd70fde42
--- /dev/null
+++ b/providers/common/ai/src/airflow/providers/common/ai/utils/sql_search.py
@@ -0,0 +1,85 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+"""Table search for 
:class:`~airflow.providers.common.ai.toolsets.sql.SQLToolset`."""
+
+from __future__ import annotations
+
+from typing import TYPE_CHECKING, Protocol
+
+if TYPE_CHECKING:
+    from collections.abc import Callable, Sequence
+
+# A table as the agent sees it: ``(qualified_name, comment)``. The name is 
exactly what
+# ``list_tables`` would return -- bare for the default schema, 
``SCHEMA.TABLE`` otherwise --
+# so it round-trips to ``get_schema``.
+TableEntry = tuple[str, str | None]
+
+
+class SQLTableSearchBackend(Protocol):
+    """
+    Finds tables relevant to a free-text query, for the ``search_tables`` tool.
+
+    The toolset filters whatever a backend returns through ``allowed_tables`` 
before the agent
+    sees it, so a backend that knows about more tables than the agent may use 
cannot widen access.
+    It can still waste its own ``limit``, so an index built for the agent 
should cover only the
+    tables the agent may use.
+    """
+
+    def search_tables(
+        self, query: str, limit: int, *, get_catalog: Callable[[], 
Sequence[TableEntry]]
+    ) -> Sequence[TableEntry]:
+        """
+        Return up to ``limit`` tables, best match first.
+
+        :param query: The agent's free-text query.
+        :param limit: Maximum number of results wanted.
+        :param get_catalog: Lists the tables the toolset exposes, introspected 
with the same
+            connection the agent queries with and cached for the toolset's 
lifetime. Comments
+            are the full text, not cut to the length the agent sees, with the 
secrets Airflow has
+            registered already masked as ``***``; the toolset cuts comments 
when it returns them
+            to the agent. A backend with its own index (a vector store, a 
search service)
+            ignores it.
+        """
+        ...
+
+
+class KeywordTableSearchBackend:
+    """
+    Default backend: case-insensitive substring match of each query word 
against table names (without their schema) and comments.
+
+    Query words are split on whitespace. A table's score is the number of 
words found in its table name
+    (the part after any schema) weighted at ``_NAME_WEIGHT`` plus the number 
found in its comment; tables scoring zero are
+    dropped, and ties are broken by name.
+    """
+
+    _NAME_WEIGHT = 3
+
+    def search_tables(
+        self, query: str, limit: int, *, get_catalog: Callable[[], 
Sequence[TableEntry]]
+    ) -> list[TableEntry]:
+        if not (words := query.casefold().split()):
+            return []
+        scored: list[tuple[int, str, TableEntry]] = []
+        for entry in get_catalog():
+            name, comment = entry
+            name_text = name.rpartition(".")[2].casefold()
+            comment_text = (comment or "").casefold()
+            score = sum(self._NAME_WEIGHT * (word in name_text) + (word in 
comment_text) for word in words)
+            if score:
+                scored.append((score, name, entry))
+        scored.sort(key=lambda item: (-item[0], item[1]))
+        return [entry for _, _, entry in scored[:limit]]
diff --git 
a/providers/common/ai/tests/unit/common/ai/toolsets/test_datafusion.py 
b/providers/common/ai/tests/unit/common/ai/toolsets/test_datafusion.py
index f149912da23..d3f0b7e81e9 100644
--- a/providers/common/ai/tests/unit/common/ai/toolsets/test_datafusion.py
+++ b/providers/common/ai/tests/unit/common/ai/toolsets/test_datafusion.py
@@ -158,6 +158,11 @@ class TestDataFusionToolsetToolPrefixModelFacingText:
         tools = asyncio.run(ts.get_tools(ctx=MagicMock(spec=RunContext)))
         assert "Call lake_get_schema again" in 
tools["lake_get_schema"].tool_def.description
 
+    def test_get_schema_description_does_not_mention_comments(self):
+        ts = DataFusionToolset([_make_mock_datasource_config()])
+        tools = asyncio.run(ts.get_tools(ctx=MagicMock(spec=RunContext)))
+        assert "comment" not in 
tools["get_schema"].tool_def.description.casefold()
+
     def test_get_schema_hint_names_the_prefixed_tool(self):
         ts = DataFusionToolset([_make_mock_datasource_config()], 
tool_prefix="lake", max_columns=2)
         ts._engine = _make_mock_engine(
diff --git a/providers/common/ai/tests/unit/common/ai/toolsets/test_sql.py 
b/providers/common/ai/tests/unit/common/ai/toolsets/test_sql.py
index cdbe3bf86a8..493b68fa80c 100644
--- a/providers/common/ai/tests/unit/common/ai/toolsets/test_sql.py
+++ b/providers/common/ai/tests/unit/common/ai/toolsets/test_sql.py
@@ -20,7 +20,7 @@ import asyncio
 import json
 import sqlite3
 import traceback
-from unittest.mock import MagicMock, PropertyMock, patch
+from unittest.mock import MagicMock, PropertyMock, call, patch
 
 import pytest
 from pydantic_ai._run_context import RunContext
@@ -32,6 +32,7 @@ from sqlalchemy.exc import OperationalError
 
 from airflow.providers.common.ai.exceptions import SQLToolsetConnectionError
 from airflow.providers.common.ai.toolsets.sql import SQLToolset
+from airflow.providers.common.ai.utils.sql_search import 
KeywordTableSearchBackend
 from airflow.providers.common.ai.utils.tool_definition import 
_SUPPORTS_RETURN_SCHEMA
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 
@@ -68,8 +69,11 @@ def _make_mock_db_hook(
     """Create a mock DbApiHook with sensible defaults."""
     mock = MagicMock(spec=DbApiHook)
     mock.inspector = MagicMock(spec=Inspector)
+    # Assigned rather than taken from the spec: SQLAlchemy 1.4's Inspector has 
no such method.
+    mock.inspector.get_multi_table_comment = MagicMock(return_value={})
     mock.inspector.get_table_names.return_value = table_names or ["users", 
"orders"]
-    mock.inspector.get_columns.return_value = table_schema or [
+    mock.get_table_comment.return_value = None
+    mock.get_table_columns_with_comments.return_value = table_schema or [
         {"name": "id", "type": "INTEGER"},
         {"name": "name", "type": "VARCHAR"},
     ]
@@ -122,6 +126,15 @@ class TestSQLToolsetGetTools:
         tools = asyncio.run(ts.get_tools(ctx=MagicMock()))
         assert set(tools.keys()) == {"list_tables", "get_schema", "query", 
"check_query"}
 
+    def test_search_tables_is_registered_only_with_a_backend(self):
+        ts = SQLToolset("pg_default", 
search_backend=KeywordTableSearchBackend())
+        tools = asyncio.run(ts.get_tools(ctx=MagicMock()))
+        assert set(tools.keys()) == {"list_tables", "get_schema", "query", 
"check_query", "search_tables"}
+        validator = tools["search_tables"].args_validator
+        assert validator.validate_python({"query": "orders"}) == {"query": 
"orders"}
+        with pytest.raises(ValidationError):
+            validator.validate_python({})
+
     def test_tool_definitions_have_descriptions(self):
         ts = SQLToolset("pg_default")
         tools = asyncio.run(ts.get_tools(ctx=MagicMock()))
@@ -214,6 +227,10 @@ class TestSQLToolsetToolPrefixModelFacingText:
         description = tools["pg_get_schema"].tool_def.description
         assert "Call pg_get_schema again" in description
 
+    def test_get_schema_description_names_the_table_comment_key(self):
+        tools = 
asyncio.run(SQLToolset("pg_default").get_tools(ctx=MagicMock()))
+        assert "`table_comment`" in tools["get_schema"].tool_def.description
+
     def test_get_schema_description_without_prefix_is_unchanged(self):
         tools = 
asyncio.run(SQLToolset("pg_default").get_tools(ctx=MagicMock()))
         assert "Call get_schema again" in 
tools["get_schema"].tool_def.description
@@ -305,7 +322,7 @@ class TestSQLToolsetGetSchema:
             "columns": [{"name": "id", "type": "INTEGER"}, {"name": "name", 
"type": "VARCHAR"}],
             "column_count": 2,
         }
-        mock_hook.inspector.get_columns.assert_called_once_with("users", 
schema=None)
+        
mock_hook.get_table_columns_with_comments.assert_called_once_with("users", 
schema=None)
 
     def test_name_contains_filters_the_columns(self):
         """``name_contains`` threads from the tool call through to the bounded 
result."""
@@ -366,13 +383,13 @@ class TestSQLToolsetGetSchema:
         assert "error" in data
         assert "secrets" in data["error"]
         # The guard must short-circuit before touching the database.
-        ts._hook.inspector.get_columns.assert_not_called()
+        ts._hook.get_table_columns_with_comments.assert_not_called()
 
     def test_introspection_error_raises_model_retry(self):
         """A failure while reading a table's schema is returned to the agent 
as a retry."""
         ts = SQLToolset("pg_default")
         ts._hook = _make_mock_db_hook()
-        ts._hook.inspector.get_columns.side_effect = Exception('relation 
"users" does not exist')
+        ts._hook.get_table_columns_with_comments.side_effect = 
Exception('relation "users" does not exist')
 
         with pytest.raises(ModelRetry) as exc_info:
             asyncio.run(
@@ -924,6 +941,420 @@ class TestSQLToolsetConnectionFailures:
         assert "postgresql://svc:***@db" in str(exc_info.value)
 
 
+class TestSQLToolsetComments:
+    @staticmethod
+    def _get_schema(ts: SQLToolset, **args) -> dict:
+        args = {"table_name": "users", **args}
+        return json.loads(asyncio.run(ts.call_tool("get_schema", args, 
ctx=MagicMock(), tool=MagicMock())))
+
+    @staticmethod
+    def _list_tables(ts: SQLToolset):
+        return json.loads(asyncio.run(ts.call_tool("list_tables", {}, 
ctx=MagicMock(), tool=MagicMock())))
+
+    def test_get_schema_returns_table_and_column_comments(self):
+        ts = SQLToolset("pg_default")
+        ts._hook = _make_mock_db_hook(
+            table_schema=[
+                {"name": "id", "type": "INTEGER", "comment": "Primary key"},
+                {"name": "name", "type": "VARCHAR"},
+            ]
+        )
+        ts._hook.get_table_comment.return_value = "Registered users"
+
+        assert self._get_schema(ts) == {
+            "columns": [
+                {"name": "id", "type": "INTEGER", "comment": "Primary key"},
+                {"name": "name", "type": "VARCHAR"},
+            ],
+            "column_count": 2,
+            "table_comment": "Registered users",
+        }
+        ts._hook.get_table_comment.assert_called_once_with("users", 
schema=None)
+
+    def test_get_schema_truncates_long_comments(self):
+        ts = SQLToolset("pg_default")
+        ts._hook = _make_mock_db_hook(table_schema=[{"name": "id", "type": 
"INTEGER", "comment": "c" * 500}])
+        ts._hook.get_table_comment.return_value = "t" * 500
+
+        data = self._get_schema(ts)
+        assert data["columns"][0]["comment"] == "c" * 199 + "\u2026"
+        assert data["table_comment"] == "t" * 199 + "\u2026"
+
+    @pytest.mark.enable_redact
+    @pytest.mark.parametrize(
+        "entry_point", ["get-schema-column", "get-schema-table", 
"list-tables", "search-tables"]
+    )
+    def test_a_secret_crossing_the_comment_cut_is_masked(self, entry_point, 
registered_secret):
+        comment = "x" * 190 + registered_secret + "y" * 50
+        ts = SQLToolset("pg_default", include_table_comments=True, 
search_backend=KeywordTableSearchBackend())
+        ts._hook = _make_mock_db_hook(
+            table_names=["users"], table_schema=[{"name": "id", "type": 
"INTEGER", "comment": comment}]
+        )
+        ts._hook.get_table_comment.return_value = comment
+        ts._hook.inspector.get_multi_table_comment.return_value = {(None, 
"users"): {"text": comment}}
+
+        if entry_point == "get-schema-column":
+            actual = self._get_schema(ts)["columns"][0]["comment"]
+        elif entry_point == "get-schema-table":
+            actual = self._get_schema(ts)["table_comment"]
+        elif entry_point == "list-tables":
+            actual = self._list_tables(ts)[0]["comment"]
+        else:
+            result = json.loads(
+                asyncio.run(
+                    ts.call_tool("search_tables", {"query": "users"}, 
ctx=MagicMock(), tool=MagicMock())
+                )
+            )
+            actual = result["tables"][0]["comment"]
+
+        assert actual == "x" * 190 + "***" + "y" * 6 + "\u2026"
+
+    def test_get_schema_survives_table_comment_failure(self):
+        ts = SQLToolset("pg_default")
+        ts._hook = _make_mock_db_hook()
+        ts._hook.get_table_comment.side_effect = Exception("permission denied")
+
+        assert self._get_schema(ts)["column_count"] == 2
+
+    def test_column_comments_are_dropped_before_the_columns(self):
+        """Columns that fit on their own are returned in full, without 
comments, once comments overflow."""
+        columns = [{"name": f"c{i}", "type": "INTEGER"} for i in range(10)]
+        with_comments = [{**col, "comment": "x" * 200} for col in columns]
+        budget = 800
+
+        plain = SQLToolset("pg_default", max_result_bytes=budget)
+        plain._hook = _make_mock_db_hook(table_schema=columns)
+        assert "truncated" not in self._get_schema(plain)
+
+        commented = SQLToolset("pg_default", max_result_bytes=budget)
+        commented._hook = _make_mock_db_hook(table_schema=with_comments)
+        data = self._get_schema(commented)
+        assert "truncated" not in data
+        assert data["columns"] == columns
+        assert data["column_count"] == 10
+        assert data["comments_omitted"] is True
+        assert "name_contains" in data["hint"]
+
+    def test_list_tables_default_shape_ignores_comments(self):
+        ts = SQLToolset("pg_default")
+        ts._hook = _make_mock_db_hook(table_names=["users", "orders"])
+
+        assert self._list_tables(ts) == ["users", "orders"]
+        ts._hook.inspector.get_multi_table_comment.assert_not_called()
+
+    def test_list_tables_with_comments_returns_name_and_comment(self):
+        ts = SQLToolset("pg_default", include_table_comments=True, 
schema="public")
+        ts._hook = _make_mock_db_hook(table_names=["users", "orders"])
+        ts._hook.inspector.get_multi_table_comment.return_value = {
+            ("public", "users"): {"text": "u" * 500},
+            ("public", "orders"): {"text": None},
+        }
+
+        assert self._list_tables(ts) == [
+            {"name": "users", "comment": "u" * 199 + "\u2026"},
+            {"name": "orders"},
+        ]
+        
ts._hook.inspector.get_multi_table_comment.assert_called_once_with(schema="public",
 filter_names=None)
+
+    def test_list_tables_with_comments_filters_and_qualifies(self):
+        ts = SQLToolset("sf", allowed_tables=["S1.A"], 
include_table_comments=True)
+        hook = _make_mock_db_hook(table_names=["A", "B"])
+        hook.inspector.get_multi_table_comment.return_value = {
+            ("S1", "A"): {"text": "table a"},
+            ("S1", "B"): {"text": "secret b"},
+        }
+        ts._hook = hook
+
+        assert self._list_tables(ts) == [{"name": "S1.A", "comment": "table 
a"}]
+
+    def 
test_list_tables_omits_comments_when_inspector_cannot_read_them_in_bulk(self):
+        """SQLAlchemy 1.4's Inspector has no get_multi_table_comment."""
+        ts = SQLToolset("pg_default", include_table_comments=True)
+        ts._hook = _make_mock_db_hook()
+        ts._hook.inspector = MagicMock(spec=["get_table_names"])
+        ts._hook.inspector.get_table_names.return_value = ["users"]
+
+        assert self._list_tables(ts) == [{"name": "users"}]
+
+    def 
test_list_tables_with_comments_degrades_when_dialect_cannot_report(self):
+        ts = SQLToolset("pg_default", include_table_comments=True)
+        ts._hook = _make_mock_db_hook(table_names=["users"])
+        ts._hook.inspector.get_multi_table_comment.side_effect = 
NotImplementedError
+
+        assert self._list_tables(ts) == [{"name": "users"}]
+
+    @pytest.mark.parametrize(
+        ("allowed_tables", "schema", "reflected", "expected_calls"),
+        [
+            pytest.param(
+                ["S1.A", "S2.C"],
+                None,
+                {"S1": ["A", "B"], "S2": ["C", "D"]},
+                [call(schema="S1", filter_names=["A"]), call(schema="S2", 
filter_names=["C"])],
+                id="qualified-per-schema",
+            ),
+            pytest.param(
+                ["users"],
+                "public",
+                {"public": ["USERS", "SECRETS"]},
+                [call(schema="public", filter_names=["USERS"])],
+                id="reflected-case-not-user-input",
+            ),
+        ],
+    )
+    def test_only_allowed_tables_comments_are_requested(
+        self, allowed_tables, schema, reflected, expected_calls
+    ):
+        ts = SQLToolset("sf", allowed_tables=allowed_tables, schema=schema, 
include_table_comments=True)
+        hook = _make_mock_db_hook()
+        hook.inspector.get_table_names.side_effect = lambda schema=None: 
reflected.get(schema, [])
+        ts._hook = hook
+
+        self._list_tables(ts)
+
+        comment_reads = hook.inspector.get_multi_table_comment
+        assert comment_reads.call_count == len(expected_calls)
+        comment_reads.assert_has_calls(expected_calls, any_order=True)
+
+    @pytest.mark.parametrize(
+        "tool",
+        [pytest.param("list_tables", id="list-tables"), 
pytest.param("search_tables", id="search-tables")],
+    )
+    def test_comment_read_failure_leaves_tables_without_comments(self, tool):
+        ts = SQLToolset("pg_default", include_table_comments=True, 
search_backend=KeywordTableSearchBackend())
+        ts._hook = _make_mock_db_hook(table_names=["users"])
+        ts._hook.inspector.get_multi_table_comment.side_effect = 
OperationalError(
+            "SELECT", {}, Exception("permission denied")
+        )
+        args = {"query": "users"} if tool == "search_tables" else {}
+
+        result = json.loads(_call(ts, tool, args))
+
+        assert result == ([{"name": "users"}] if tool == "list_tables" else 
{"tables": [{"name": "users"}]})
+
+    @pytest.mark.parametrize(
+        "tool",
+        [pytest.param("list_tables", id="list-tables"), 
pytest.param("search_tables", id="search-tables")],
+    )
+    def test_comment_read_on_lost_connection_raises_and_is_not_cached(self, 
tool):
+        ts = SQLToolset("pg_default", include_table_comments=True, 
search_backend=KeywordTableSearchBackend())
+        ts._hook = _make_mock_db_hook(table_names=["users"])
+        ts._hook.inspector.get_multi_table_comment.side_effect = 
OperationalError(
+            "SELECT",
+            {},
+            sqlite3.OperationalError("server closed the connection"),
+            connection_invalidated=True,
+        )
+        args = {"query": "users"} if tool == "search_tables" else {}
+
+        with pytest.raises(SQLToolsetConnectionError, match="server closed the 
connection"):
+            _call(ts, tool, args)
+
+        assert ts._catalog is None
+
+
+class _FakeSearchBackend:
+    """Returns canned results, ignoring the query, so tests control exactly 
what the toolset must filter."""
+
+    def __init__(self, results: list[tuple[str, str | None]]) -> None:
+        self.results = results
+        self.limits: list[int] = []
+
+    def search_tables(self, query, limit, *, get_catalog):
+        self.limits.append(limit)
+        return self.results
+
+
+class _CatalogRecordingBackend:
+    """Records the catalog it is handed, so tests can see what a custom 
backend receives."""
+
+    def __init__(self) -> None:
+        self.catalogs: list = []
+
+    def search_tables(self, query, limit, *, get_catalog):
+        self.catalogs.append(list(get_catalog()))
+        return []
+
+
+class TestSQLToolsetSearchTables:
+    @staticmethod
+    def _call(ts: SQLToolset, name: str, args: dict | None = None):
+        return json.loads(asyncio.run(ts.call_tool(name, args or {}, 
ctx=MagicMock(), tool=MagicMock())))
+
+    @pytest.mark.enable_redact
+    def test_backend_receives_full_masked_comments(self, registered_secret):
+        backend = _CatalogRecordingBackend()
+        ts = SQLToolset("pg_default", search_backend=backend)
+        ts._hook = _make_mock_db_hook(table_names=["users"])
+        ts._hook.inspector.get_multi_table_comment.return_value = {
+            (None, "users"): {"text": "x" * 190 + registered_secret + "y" * 50}
+        }
+
+        self._call(ts, "search_tables", {"query": "users"})
+
+        assert backend.catalogs == [[("users", "x" * 190 + "***" + "y" * 50)]]
+
+    def test_default_backend_matches_words_past_the_output_cut(self):
+        ts = SQLToolset("pg_default", 
search_backend=KeywordTableSearchBackend())
+        ts._hook = _make_mock_db_hook(table_names=["ledger"])
+        ts._hook.inspector.get_multi_table_comment.return_value = {
+            (None, "ledger"): {"text": "a" * 300 + " invoices"}
+        }
+
+        result = self._call(ts, "search_tables", {"query": "invoices"})
+
+        assert result == {"tables": [{"name": "ledger", "comment": "a" * 199 + 
"\u2026"}]}
+
+    def test_results_outside_allowed_tables_are_filtered(self):
+        """A backend that knows about more tables than the agent may use 
cannot widen access."""
+        backend = _FakeSearchBackend([("secrets", "passwords"), ("orders", 
"Orders"), ("users", None)])
+        ts = SQLToolset("pg_default", allowed_tables=["orders", "users"], 
search_backend=backend)
+
+        result = self._call(ts, "search_tables", {"query": "anything"})
+
+        assert result == {"tables": [{"name": "orders", "comment": "Orders"}, 
{"name": "users"}]}
+
+    def 
test_qualified_results_are_matched_against_qualified_allowed_tables(self):
+        backend = _FakeSearchBackend([("S1.A", None), ("S2.A", None), ("A", 
None)])
+        ts = SQLToolset("sf", allowed_tables=["S1.A"], search_backend=backend)
+
+        assert self._call(ts, "search_tables", {"query": "a"}) == {"tables": 
[{"name": "S1.A"}]}
+
+    @pytest.mark.parametrize(
+        ("requested", "expected_limit"),
+        [
+            pytest.param(None, 10, id="default"),
+            pytest.param(3, 3, id="explicit"),
+            pytest.param(1000, 50, id="capped"),
+            pytest.param(-5, 1, id="floored"),
+            pytest.param(0, 1, id="zero-is-floored-not-defaulted"),
+        ],
+    )
+    def test_limit_is_bounded(self, requested, expected_limit):
+        backend = _FakeSearchBackend([])
+        ts = SQLToolset("pg_default", search_backend=backend)
+        args = {"query": "x"} if requested is None else {"query": "x", 
"limit": requested}
+
+        self._call(ts, "search_tables", args)
+
+        assert backend.limits == [expected_limit]
+
+    def test_results_are_cut_to_limit_after_filtering(self):
+        backend = _FakeSearchBackend([("secret", None), ("a", None), ("b", 
None), ("c", None)])
+        ts = SQLToolset("pg_default", allowed_tables=["a", "b", "c"], 
search_backend=backend)
+
+        result = self._call(ts, "search_tables", {"query": "x", "limit": 2})
+
+        assert [t["name"] for t in result["tables"]] == ["a", "b"]
+
+    def test_result_comments_are_truncated(self):
+        backend = _FakeSearchBackend([("orders", "c" * 500)])
+        ts = SQLToolset("pg_default", search_backend=backend)
+
+        result = self._call(ts, "search_tables", {"query": "x"})
+
+        assert result["tables"][0]["comment"] == "c" * 199 + "\u2026"
+
+    @pytest.mark.enable_redact
+    def 
test_custom_backend_comment_with_a_secret_crossing_the_cut_is_masked(self, 
registered_secret):
+        backend = _FakeSearchBackend([("users", "x" * 190 + registered_secret 
+ "y" * 50)])
+        ts = SQLToolset("pg_default", search_backend=backend)
+
+        result = self._call(ts, "search_tables", {"query": "x"})
+
+        assert result["tables"][0]["comment"] == "x" * 190 + "***" + "y" * 6 + 
"\u2026"
+
+    def test_default_backend_introspects_once_with_the_toolsets_hook(self):
+        ts = SQLToolset(
+            "pg_default", allowed_tables=["users", "orders"], 
search_backend=KeywordTableSearchBackend()
+        )
+        ts._hook = _make_mock_db_hook(table_names=["users", "orders", 
"secrets"])
+        ts._hook.inspector.get_multi_table_comment.return_value = {
+            (None, "users"): {"text": "Registered customers"},
+            (None, "orders"): {"text": None},
+            (None, "secrets"): {"text": "customers passwords"},
+        }
+
+        first = self._call(ts, "search_tables", {"query": "customers"})
+        second = self._call(ts, "search_tables", {"query": "orders"})
+
+        assert first == {"tables": [{"name": "users", "comment": "Registered 
customers"}]}
+        assert second == {"tables": [{"name": "orders"}]}
+        ts._hook.inspector.get_table_names.assert_called_once_with(schema=None)
+        ts._hook.inspector.get_multi_table_comment.assert_called_once_with(
+            schema=None, filter_names=["users", "orders"]
+        )
+
+    def test_degraded_catalog_is_cached(self):
+        ts = SQLToolset("pg_default", include_table_comments=True, 
search_backend=KeywordTableSearchBackend())
+        ts._hook = _make_mock_db_hook(table_names=["users"])
+        ts._hook.inspector.get_multi_table_comment.side_effect = 
OperationalError(
+            "SELECT", {}, Exception("permission denied")
+        )
+
+        self._call(ts, "search_tables", {"query": "users"})
+        self._call(ts, "search_tables", {"query": "users"})
+
+        assert ts._hook.inspector.get_multi_table_comment.call_count == 1
+
+    def 
test_search_omits_comments_when_inspector_cannot_read_them_in_bulk(self):
+        """SQLAlchemy 1.4's Inspector has no get_multi_table_comment."""
+        ts = SQLToolset("pg_default", 
search_backend=KeywordTableSearchBackend())
+        ts._hook = _make_mock_db_hook()
+        ts._hook.inspector = MagicMock(spec=["get_table_names"])
+        ts._hook.inspector.get_table_names.return_value = ["users"]
+
+        assert self._call(ts, "search_tables", {"query": "users"}) == 
{"tables": [{"name": "users"}]}
+
+    def test_calling_search_tables_without_a_backend_is_unknown(self):
+        ts = SQLToolset("pg_default")
+        ts._hook = _make_mock_db_hook()
+
+        with pytest.raises(ValueError, match="Unknown tool: 'search_tables'"):
+            asyncio.run(ts.execute_tool("search_tables", {"query": "x"}, 
ctx=MagicMock(), tool=MagicMock()))
+
+    @pytest.mark.parametrize(
+        ("backend", "mentioned"),
+        [
+            pytest.param(None, False, id="no-backend"),
+            pytest.param(_FakeSearchBackend([]), True, id="with-backend"),
+        ],
+    )
+    def test_error_hint_points_at_search_tables_only_when_registered(self, 
backend, mentioned):
+        ts = SQLToolset("pg_default", search_backend=backend)
+        ts._hook = _make_mock_db_hook()
+        ts._hook.inspector.get_table_names.side_effect = Exception("boom")
+
+        with pytest.raises(ModelRetry) as exc_info:
+            asyncio.run(ts.call_tool("list_tables", {}, ctx=MagicMock(), 
tool=MagicMock()))
+
+        assert ("search_tables" in exc_info.value.message) is mentioned
+
+    @pytest.mark.parametrize(
+        ("table_count", "listed"),
+        [pytest.param(3, True, id="at-threshold"), pytest.param(4, False, 
id="over-threshold")],
+    )
+    def test_list_tables_stops_listing_over_max_listed_tables(self, 
table_count, listed):
+        names = [f"t{i}" for i in range(table_count)]
+        ts = SQLToolset("pg_default", search_backend=_FakeSearchBackend([]), 
max_listed_tables=3)
+        ts._hook = _make_mock_db_hook(table_names=names)
+
+        result = self._call(ts, "list_tables")
+
+        if listed:
+            assert result == names
+        else:
+            assert result["table_count"] == table_count
+            assert "search_tables" in result["hint"]
+
+    def test_list_tables_has_no_limit_without_a_backend(self):
+        names = [f"t{i}" for i in range(150)]
+        ts = SQLToolset("pg_default", max_listed_tables=3)
+        ts._hook = _make_mock_db_hook(table_names=names)
+
+        assert self._call(ts, "list_tables") == names
+
+
 class TestSQLToolsetMultiSchema:
     """Schema-qualified allowed_tables span multiple schemas in one 
database."""
 
@@ -932,7 +1363,8 @@ class TestSQLToolsetMultiSchema:
         hook = MagicMock(spec=DbApiHook)
         hook.inspector = MagicMock()
         hook.inspector.get_table_names.side_effect = lambda schema=None: 
tables_by_schema.get(schema, [])
-        hook.inspector.get_columns.return_value = [{"name": "id", "type": 
"INTEGER"}]
+        hook.get_table_columns_with_comments.return_value = [{"name": "id", 
"type": "INTEGER"}]
+        hook.get_table_comment.return_value = None
         return hook
 
     def test_list_tables_spans_multiple_schemas(self):
@@ -984,7 +1416,7 @@ class TestSQLToolsetMultiSchema:
             )
         )
         assert result == {"columns": [{"name": "id", "type": "INTEGER"}], 
"column_count": 1}
-        ts._hook.inspector.get_columns.assert_called_once_with(
+        ts._hook.get_table_columns_with_comments.assert_called_once_with(
             "DEPLOYMENT_IMAGE_DETAILS", schema="MODEL_ASTRO"
         )
 
@@ -1000,14 +1432,14 @@ class TestSQLToolsetMultiSchema:
             )
         )
         assert "error" in result
-        ts._hook.inspector.get_columns.assert_not_called()
+        ts._hook.get_table_columns_with_comments.assert_not_called()
 
     def test_get_schema_unqualified_uses_default_schema(self):
         ts = SQLToolset("pg", schema="public")
         ts._hook = self._schema_aware_hook({"public": ["users"]})
 
         asyncio.run(ts.call_tool("get_schema", {"table_name": "users"}, 
ctx=MagicMock(), tool=MagicMock()))
-        ts._hook.inspector.get_columns.assert_called_once_with("users", 
schema="public")
+        
ts._hook.get_table_columns_with_comments.assert_called_once_with("users", 
schema="public")
 
     def test_list_tables_matches_case_insensitively(self):
         """Snowflake reflects unquoted names lowercased; uppercase 
allowed_tables still match."""
@@ -1040,7 +1472,7 @@ class TestSQLToolsetMultiSchema:
             )
         )
         assert "error" not in result
-        ts._hook.inspector.get_columns.assert_called_once_with(
+        ts._hook.get_table_columns_with_comments.assert_called_once_with(
             "deployment_image_details", schema="MODEL_ASTRO"
         )
 
diff --git 
a/providers/common/ai/tests/unit/common/ai/utils/test_query_results.py 
b/providers/common/ai/tests/unit/common/ai/utils/test_query_results.py
index 3ab3a2428ca..8dc042768ac 100644
--- a/providers/common/ai/tests/unit/common/ai/utils/test_query_results.py
+++ b/providers/common/ai/tests/unit/common/ai/utils/test_query_results.py
@@ -27,6 +27,7 @@ from airflow.providers.common.ai.utils.query_results import (
     build_get_schema_description,
     build_query_result,
     build_schema_result,
+    truncate_comment,
 )
 
 
@@ -176,6 +177,13 @@ def 
test_a_secret_that_json_escapes_is_masked_in_the_rows(register_secret):
     assert result["rows"] == [["admin", "***"]]
 
 
[email protected]_redact
+def test_truncate_comment_masks_a_secret_before_cutting(registered_secret):
+    comment = "x" * 190 + registered_secret + "y" * 50
+
+    assert truncate_comment(comment) == "x" * 190 + "***" + "y" * 6 + "\u2026"
+
+
 def _schema(columns, *, max_columns=100, max_result_bytes=65_536, 
name_contains=None) -> dict:
     return json.loads(
         build_schema_result(
@@ -344,6 +352,19 @@ class TestSchemaToolName:
     def test_description_defaults_to_the_bare_name(self):
         assert "Call get_schema again" in build_get_schema_description()
 
+    @pytest.mark.parametrize(
+        ("include_comments", "mentions_comments"),
+        [
+            pytest.param(True, True, id="with-comments"),
+            pytest.param(False, False, id="without-comments"),
+        ],
+    )
+    def test_description_mentions_comments_only_when_asked(self, 
include_comments, mentions_comments):
+        description = 
build_get_schema_description(include_comments=include_comments)
+        assert ("`table_comment`" in description) is mentions_comments
+        assert ("`comments_omitted`" in description) is mentions_comments
+        assert ("comment" in description.casefold()) is mentions_comments
+
     def test_hints_name_the_given_tool(self):
         no_match = json.loads(
             build_schema_result(
@@ -361,3 +382,83 @@ class TestSchemaToolName:
         )
         assert "Call pg_get_schema without name_contains" in no_match["hint"]
         assert "Call pg_get_schema with a name_contains substring" in 
summary["hint"]
+
+
[email protected](
+    ("comment", "expected"),
+    [
+        pytest.param("short", "short", id="under-limit"),
+        pytest.param("a" * 200, "a" * 200, id="at-limit"),
+        pytest.param("a" * 201, "a" * 199 + "\u2026", id="over-limit"),
+    ],
+)
+def test_truncate_comment(comment, expected):
+    assert truncate_comment(comment) == expected
+
+
+class TestSchemaComments:
+    def test_table_comment_is_returned_only_when_present(self):
+        with_comment = json.loads(
+            build_schema_result(
+                _cols(2), max_columns=100, max_result_bytes=65_536, 
table_comment="Customer orders"
+            )
+        )
+        without = json.loads(build_schema_result(_cols(2), max_columns=100, 
max_result_bytes=65_536))
+        assert with_comment["table_comment"] == "Customer orders"
+        assert "table_comment" not in without
+
+    def test_summary_keeps_table_comment_and_drops_column_comments(self):
+        cols = [{**col, "comment": "note"} for col in _cols(250)]
+        data = json.loads(
+            build_schema_result(cols, max_columns=100, 
max_result_bytes=65_536, table_comment="Wide table")
+        )
+        assert data["truncated_by"] == "max_columns"
+        assert data["table_comment"] == "Wide table"
+        assert data["comments_omitted"] is True
+        assert "comments are omitted" in data["hint"]
+        assert all("comment" not in col for col in data["sample_columns"])
+
+    @pytest.mark.parametrize(
+        ("name_contains", "advice"),
+        [
+            pytest.param(None, "with a name_contains substring", 
id="unfiltered"),
+            pytest.param("col", "more specific name_contains", id="filtered"),
+        ],
+    )
+    def test_column_comments_are_dropped_before_summarizing(self, 
name_contains, advice):
+        cols = _cols(10)
+        data = json.loads(
+            build_schema_result(
+                [{**col, "comment": "x" * 200} for col in cols],
+                max_columns=100,
+                max_result_bytes=1000,
+                name_contains=name_contains,
+                table_comment="Wide",
+            )
+        )
+        assert data["columns"] == cols
+        assert "truncated" not in data
+        assert data["comments_omitted"] is True
+        assert data["table_comment"] == "Wide"
+        assert advice in data["hint"]
+        if name_contains is not None:
+            assert data["name_contains"] == name_contains
+            assert data["total_columns"] == 10
+
+    def test_columns_too_wide_even_without_comments_are_summarized(self):
+        data = json.loads(
+            build_schema_result(
+                [{**col, "comment": "x" * 200} for col in _cols(10, prefix="c" 
* 100)],
+                max_columns=100,
+                max_result_bytes=1000,
+            )
+        )
+        assert data["truncated_by"] == "max_result_bytes"
+        assert data["comments_omitted"] is True
+        assert "columns" not in data
+        assert data["sample_columns"]
+
+    def test_summary_without_comments_is_unchanged(self):
+        data = json.loads(build_schema_result(_cols(250), max_columns=100, 
max_result_bytes=65_536))
+        assert "comments_omitted" not in data
+        assert "table_comment" not in data
diff --git a/providers/common/ai/tests/unit/common/ai/utils/test_sql_search.py 
b/providers/common/ai/tests/unit/common/ai/utils/test_sql_search.py
new file mode 100644
index 00000000000..56908be021b
--- /dev/null
+++ b/providers/common/ai/tests/unit/common/ai/utils/test_sql_search.py
@@ -0,0 +1,70 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+from __future__ import annotations
+
+import pytest
+
+from airflow.providers.common.ai.utils.sql_search import 
KeywordTableSearchBackend
+
+CATALOG = [
+    ("orders", "Customer purchase orders"),
+    ("order_items", None),
+    ("users", "Registered customers and their ORDER history"),
+    ("audit_log", "Security events"),
+]
+
+
+def _search(query: str, limit: int = 10) -> list[str]:
+    result = KeywordTableSearchBackend().search_tables(query, limit, 
get_catalog=lambda: CATALOG)
+    return [name for name, _ in result]
+
+
+class TestKeywordTableSearchBackend:
+    @pytest.mark.parametrize(
+        ("query", "expected"),
+        [
+            pytest.param("ORDERS", ["orders"], id="case-insensitive"),
+            pytest.param("order", ["orders", "order_items", "users"], 
id="name-hits-outrank-comment-hits"),
+            pytest.param("customer order", ["orders", "order_items", "users"], 
id="multiple-words-add-up"),
+            pytest.param("security", ["audit_log"], id="comment-only-match"),
+            pytest.param("zzz", [], id="no-match"),
+            pytest.param("   ", [], id="blank-query"),
+        ],
+    )
+    def test_ranking(self, query, expected):
+        assert _search(query) == expected
+
+    @pytest.mark.parametrize(
+        ("query", "expected"),
+        [
+            pytest.param("crm", ["MODEL_CRM.CRM_CONTACTS"], id="schema-word"),
+            pytest.param("astro", ["MODEL_CRM.SF_ASTRO_ORGS"], 
id="table-part-still-matches"),
+        ],
+    )
+    def test_schema_is_not_matched_as_part_of_the_name(self, query, expected):
+        catalog = [
+            ("MODEL_CRM.SF_ASTRO_ORGS", "Salesforce organisations"),
+            ("MODEL_CRM.CRM_CONTACTS", None),
+            ("MODEL_ASTRO.DEPLOYMENT", "Deployments"),
+        ]
+        result = KeywordTableSearchBackend().search_tables(query, 10, 
get_catalog=lambda: catalog)
+        assert [name for name, _ in result] == expected
+
+    def test_ties_break_by_name_and_limit_applies(self):
+        catalog = [("b_t", None), ("a_t", None), ("c_t", None)]
+        result = KeywordTableSearchBackend().search_tables("t", 2, 
get_catalog=lambda: catalog)
+        assert result == [("a_t", None), ("b_t", None)]
diff --git a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py 
b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
index fe5c3e9e673..03f3be87514 100644
--- a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
+++ b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
@@ -394,6 +394,41 @@ class DbApiHook(BaseHook):
         columns = self.inspector.get_columns(table_name, schema=schema)
         return [{"name": col["name"], "type": str(col["type"])} for col in 
columns]
 
+    def get_table_comment(self, table_name: str, schema: str | None = None) -> 
str | None:
+        """
+        Return the table comment using SQLAlchemy Inspector.
+
+        :param table_name: Name of the table.
+        :param schema: Optional schema/namespace name.
+        :return: The comment, or ``None`` when the table has none or the 
dialect cannot report comments.
+        """
+        try:
+            comment = self.inspector.get_table_comment(table_name, 
schema=schema)
+        except NotImplementedError:
+            return None
+        return comment.get("text") or None
+
+    def get_table_columns_with_comments(
+        self, table_name: str, schema: str | None = None
+    ) -> list[dict[str, str]]:
+        """
+        Return column names and types for a table, plus each column's comment 
where one exists.
+
+        Same as :meth:`get_table_schema`, except that a column carries a 
``comment`` key
+        only when the dialect reports a non-empty comment for it.
+
+        :param table_name: Name of the table.
+        :param schema: Optional schema/namespace name.
+        :return: List of dicts with ``name`` and ``type`` keys, and 
``comment`` when present.
+        """
+        result: list[dict[str, str]] = []
+        for col in self.inspector.get_columns(table_name, schema=schema):
+            column = {"name": col["name"], "type": str(col["type"])}
+            if comment := col.get("comment"):
+                column["comment"] = comment
+            result.append(column)
+        return result
+
     @cached_property
     def dialect_name(self) -> str:
         if make_url is not None:
diff --git 
a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi 
b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
index fab16ced47e..7f605f0a58d 100644
--- a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
+++ b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
@@ -97,6 +97,10 @@ class DbApiHook(BaseHook):
     def get_sqlalchemy_engine(self, engine_kwargs: Incomplete | None = None) 
-> Engine: ...
     @cached_property
     def inspector(self) -> Inspector: ...
+    def get_table_comment(self, table_name: str, schema: str | None = None) -> 
str | None: ...
+    def get_table_columns_with_comments(
+        self, table_name: str, schema: str | None = None
+    ) -> list[dict[str, str]]: ...
     @cached_property
     def dialect_name(self) -> str: ...
     @cached_property
diff --git a/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py 
b/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
index 3207721367a..f3930e6e87f 100644
--- a/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
+++ b/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
@@ -28,6 +28,7 @@ import pandas as pd
 import polars as pl
 import pytest
 from sqlalchemy import event
+from sqlalchemy.engine import Inspector
 
 from airflow.exceptions import AirflowProviderDeprecationWarning
 from airflow.models import Connection
@@ -464,6 +465,65 @@ class TestDbApiHookGetTableSchema:
         mock_inspector.get_columns.assert_called_once_with("my_table", 
schema="my_schema")
 
 
+class TestDbApiHookGetTableComments:
+    @pytest.mark.db_test
+    @pytest.mark.parametrize(
+        ("inspector_result", "expected"),
+        [
+            pytest.param({"text": "Customer orders"}, "Customer orders", 
id="has-comment"),
+            pytest.param({"text": None}, None, id="comment-is-none"),
+            pytest.param({}, None, id="no-text-key"),
+            pytest.param(NotImplementedError(), None, 
id="dialect-unsupported"),
+        ],
+    )
+    def test_get_table_comment(self, inspector_result, expected):
+        dbapi_hook = mock_db_hook(DbApiHook)
+        mock_inspector = MagicMock(spec=Inspector)
+        if isinstance(inspector_result, Exception):
+            mock_inspector.get_table_comment.side_effect = inspector_result
+        else:
+            mock_inspector.get_table_comment.return_value = inspector_result
+        with patch.object(
+            type(dbapi_hook), "inspector", new_callable=PropertyMock, 
return_value=mock_inspector
+        ):
+            result = dbapi_hook.get_table_comment("orders", schema="sales")
+
+        assert result == expected
+        mock_inspector.get_table_comment.assert_called_once_with("orders", 
schema="sales")
+
+    @pytest.mark.db_test
+    def test_get_table_columns_with_comments(self):
+        dbapi_hook = mock_db_hook(DbApiHook)
+        mock_inspector = MagicMock(spec=Inspector)
+        mock_inspector.get_columns.return_value = [
+            {"name": "id", "type": "INTEGER", "comment": "Primary key"},
+            {"name": "name", "type": "VARCHAR(255)", "comment": None},
+            {"name": "note", "type": "TEXT", "comment": ""},
+            {"name": "created", "type": "TIMESTAMP"},
+        ]
+        with patch.object(
+            type(dbapi_hook), "inspector", new_callable=PropertyMock, 
return_value=mock_inspector
+        ):
+            result = dbapi_hook.get_table_columns_with_comments("users", 
schema="s")
+
+        assert result == [
+            {"name": "id", "type": "INTEGER", "comment": "Primary key"},
+            {"name": "name", "type": "VARCHAR(255)"},
+            {"name": "note", "type": "TEXT"},
+            {"name": "created", "type": "TIMESTAMP"},
+        ]
+        mock_inspector.get_columns.assert_called_once_with("users", schema="s")
+
+    def test_comment_methods_on_dialect_without_comment_support(self, 
tmp_path):
+        db_path = tmp_path / "plain.db"
+        with closing(sqlite3.connect(db_path)) as conn:
+            conn.execute("CREATE TABLE users (id INTEGER)")
+        hook = DBApiHookForTests(conn_id=DEFAULT_CONN_ID)
+        with patch.object(hook, "get_uri", autospec=True, 
return_value=f"sqlite:///{db_path}"):
+            assert hook.get_table_comment("users") is None
+            assert hook.get_table_columns_with_comments("users") == [{"name": 
"id", "type": "INTEGER"}]
+
+
 def test_inspector_is_cached():
     """inspector should return the same object on repeated access (not create 
N engines)."""
     hook = DBApiHookForTests(conn_id=DEFAULT_CONN_ID)

Reply via email to