MCPQL

Instructions

Implementation Reference

• src/tools/tableAnalysis.ts:8-204 (handler)

  The main execution handler for the mcp_table_analysis tool. Performs comprehensive analysis of a SQL Server table by querying system catalogs for columns (with types, nullability, defaults, descriptions), primary keys, foreign keys (with actions), indexes (with columns and included), constraints (check/default), and basic table info (rows, description). Uses parallel queries for efficiency.
  export const mcp_table_analysis = async (args: { table_name: string }): Promise<ToolResult<{ columns: any[]; primary_keys: any[]; foreign_keys: any[]; indexes: any[]; constraints: any[]; table_info: any; }>> => { const { table_name } = args; console.log('Executing mcp_table_analysis with:', { table_name }); const normalizedTableName = normalizeSqlObjectName(table_name); try { const pool = getPool(); // Enhanced query for columns - matching Python implementation const columnsQuery = ` SELECT c.name AS column_name, t.name AS data_type, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity, c.is_computed, cc.definition AS computed_definition, CAST(ep.value AS NVARCHAR(MAX)) AS description, c.column_id, ISNULL(dc.definition, '') AS default_value FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT JOIN sys.computed_columns cc ON c.object_id = cc.object_id AND c.column_id = cc.column_id LEFT JOIN sys.extended_properties ep ON c.object_id = ep.major_id AND c.column_id = ep.minor_id LEFT JOIN sys.default_constraints dc ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE c.object_id = OBJECT_ID(@table_name) ORDER BY c.column_id; `; // Enhanced query for primary keys - matching Python implementation const primaryKeysQuery = ` SELECT i.name AS index_name, c.name AS column_name, ic.key_ordinal FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID(@table_name) AND i.is_primary_key = 1 ORDER BY ic.key_ordinal; `; // Enhanced query for foreign keys - matching Python implementation const foreignKeysQuery = ` SELECT fk.name AS constraint_name, pc.name AS parent_column, rc.name AS referenced_column, ro.name AS referenced_table, rs.name AS referenced_schema, fk.delete_referential_action_desc, fk.update_referential_action_desc FROM sys.foreign_keys fk INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id INNER JOIN sys.columns pc ON fkc.parent_object_id = pc.object_id AND fkc.parent_column_id = pc.column_id INNER JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id INNER JOIN sys.objects ro ON fkc.referenced_object_id = ro.object_id INNER JOIN sys.schemas rs ON ro.schema_id = rs.schema_id WHERE fk.parent_object_id = OBJECT_ID(@table_name) ORDER BY fk.name, fkc.constraint_column_id; `; // Enhanced query for indexes - matching Python implementation const indexesQuery = ` SELECT i.name AS index_name, i.type_desc AS index_type, i.is_unique, i.is_primary_key, STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS columns, STRING_AGG( CASE WHEN ic.is_included_column = 1 THEN c.name END, ', ' ) WITHIN GROUP (ORDER BY ic.key_ordinal) AS included_columns FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID(@table_name) AND i.type > 0 -- Exclude heaps GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key ORDER BY i.name; `; // Enhanced query for constraints - matching Python implementation const constraintsQuery = ` SELECT cc.name AS constraint_name, cc.type_desc AS constraint_type, cc.definition AS constraint_definition, c.name AS column_name FROM sys.check_constraints cc LEFT JOIN sys.columns c ON cc.parent_object_id = c.object_id AND cc.parent_column_id = c.column_id WHERE cc.parent_object_id = OBJECT_ID(@table_name) UNION ALL SELECT dc.name AS constraint_name, 'DEFAULT_CONSTRAINT' AS constraint_type, dc.definition AS constraint_definition, c.name AS column_name FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE dc.parent_object_id = OBJECT_ID(@table_name) ORDER BY constraint_name; `; // Enhanced query for table information const tableInfoQuery = ` SELECT t.name AS table_name, s.name AS schema_name, t.create_date, t.modify_date, ISNULL(p.row_count, 0) AS row_count, CAST(ep.value AS NVARCHAR(MAX)) AS table_description FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id LEFT JOIN sys.dm_db_partition_stats p ON t.object_id = p.object_id AND p.index_id IN (0, 1) LEFT JOIN sys.extended_properties ep ON t.object_id = ep.major_id AND ep.minor_id = 0 AND ep.name = 'MS_Description' WHERE t.object_id = OBJECT_ID(@table_name); `; // Execute all queries in parallel const [columnsResult, primaryKeysResult, foreignKeysResult, indexesResult, constraintsResult, tableInfoResult] = await Promise.all([ pool.request().input('table_name', normalizedTableName).query(columnsQuery), pool.request().input('table_name', normalizedTableName).query(primaryKeysQuery), pool.request().input('table_name', normalizedTableName).query(foreignKeysQuery), pool.request().input('table_name', normalizedTableName).query(indexesQuery), pool.request().input('table_name', normalizedTableName).query(constraintsQuery), pool.request().input('table_name', normalizedTableName).query(tableInfoQuery) ]); return { success: true, data: { columns: columnsResult.recordset, primary_keys: primaryKeysResult.recordset, foreign_keys: foreignKeysResult.recordset, indexes: indexesResult.recordset, constraints: constraintsResult.recordset, table_info: tableInfoResult.recordset[0] || null } }; } catch (error: any) { console.error(`Error in mcp_table_analysis for table ${table_name}: ${error.message}`); return { success: false, error: error.message }; } };
• src/tools.ts:3-16 (schema)

  The input schema and metadata definition for the mcp_table_analysis tool, used for validation and tool discovery via ListTools.
  { name: "mcp_table_analysis", description: "Comprehensive SQL Server table analysis including structure, columns, keys, indexes, and constraints", inputSchema: { type: "object", properties: { table_name: { type: "string", description: "Fully qualified table name (schema.table), e.g. \"dbo.Users\" or \"api.Idiomas\"" } }, required: ["table_name"] } },
• src/server.ts:64-66 (registration)

  MCP server registration for ListTools request, returning the array of tool definitions including mcp_table_analysis.
  server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: MCP_MSQL_TOOLS }));
• src/server.ts:70-73 (registration)

  Dynamic tool handler resolution in CallToolRequestSchema handler, mapping tool name to imported handler function via toolHandlers[toolName].
  server.setRequestHandler(CallToolRequestSchema, async (request) => { const toolName = request.params.name; const input = request.params.arguments; const handler = (toolHandlers as { [key: string]: (args: any) => Promise<any> })[toolName];
• src/tools/index.ts:9-10 (helper)

  Re-export of the mcp_table_analysis handler from its module for centralized import in mcp-server.ts.
  export { mcp_table_analysis // Complete table analysis