SQL Server MCP

Instructions

Implementation Reference

• src/mssql_mcp/tools/tables.py:81-216 (handler)

  The core handler function for the 'describe_table' MCP tool. Decorated with @mcp.tool() for automatic registration. Executes SQL queries against MSSQL INFORMATION_SCHEMA and sys tables to retrieve comprehensive table metadata including columns, primary keys, foreign keys, and indexes. Handles parsing of table name, error handling, and formats output as a structured dictionary.
  @mcp.tool() def describe_table(table: str) -> dict[str, Any]: """Get detailed column information for a table. Retrieves column definitions, primary keys, foreign keys, and indexes. Args: table: Table name, optionally with schema (e.g., 'dbo.Users' or 'Users'). Defaults to 'dbo' schema if not specified. Returns: Dictionary with: - table: Full table name (schema.table) - columns: List of column info (name, type, nullable, etc.) - primary_key: List of primary key column names - foreign_keys: List of foreign key relationships - indexes: List of index info """ try: manager = get_connection_manager() schema, table_name = parse_table_name(table) # Get columns columns_query = """ SELECT COLUMN_NAME as [name], DATA_TYPE as [type], CHARACTER_MAXIMUM_LENGTH as [max_length], NUMERIC_PRECISION as [precision], NUMERIC_SCALE as [scale], IS_NULLABLE as [nullable], COLUMN_DEFAULT as [default_value] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s ORDER BY ORDINAL_POSITION """ columns_rows = manager.execute_query(columns_query, (schema, table_name)) columns = [] for row in columns_rows: col_info: dict[str, Any] = { "name": row["name"], "type": row["type"], "nullable": row["nullable"] == "YES", } if row["max_length"]: col_info["max_length"] = row["max_length"] if row["precision"]: col_info["precision"] = row["precision"] if row["scale"]: col_info["scale"] = row["scale"] if row["default_value"]: col_info["default"] = row["default_value"] columns.append(col_info) # Get primary key columns pk_query = """ SELECT c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON tc.CONSTRAINT_NAME = c.CONSTRAINT_NAME AND tc.TABLE_SCHEMA = c.TABLE_SCHEMA AND tc.TABLE_NAME = c.TABLE_NAME WHERE tc.TABLE_SCHEMA = %s AND tc.TABLE_NAME = %s AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY c.ORDINAL_POSITION """ pk_rows = manager.execute_query(pk_query, (schema, table_name)) primary_key = [row["COLUMN_NAME"] for row in pk_rows] # Get foreign keys fk_query = """ SELECT fk.name as constraint_name, COL_NAME(fkc.parent_object_id, fkc.parent_column_id) as [column], OBJECT_SCHEMA_NAME(fkc.referenced_object_id) as ref_schema, OBJECT_NAME(fkc.referenced_object_id) as ref_table, COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) as ref_column FROM sys.foreign_keys fk JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id WHERE fk.parent_object_id = OBJECT_ID(%s) ORDER BY fk.name, fkc.constraint_column_id """ fk_rows = manager.execute_query(fk_query, (f"{schema}.{table_name}",)) foreign_keys = [ { "constraint": row["constraint_name"], "column": row["column"], "references_table": f"{row['ref_schema']}.{row['ref_table']}", "references_column": row["ref_column"], } for row in fk_rows ] # Get indexes idx_query = """ SELECT i.name as index_name, i.type_desc as [type], i.is_unique, i.is_primary_key, STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) as [columns] FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID(%s) AND i.name IS NOT NULL GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key ORDER BY i.name """ idx_rows = manager.execute_query(idx_query, (f"{schema}.{table_name}",)) indexes = [ { "name": row["index_name"], "type": row["type"], "is_unique": bool(row["is_unique"]), "is_primary_key": bool(row["is_primary_key"]), "columns": row["columns"], } for row in idx_rows ] return { "table": f"{schema}.{table_name}", "columns": columns, "primary_key": primary_key, "foreign_keys": foreign_keys, "indexes": indexes, } except Exception as e: logger.error(f"Error describing table {table}: {e}") return {"error": str(e)}