MCPQL

Instructions

Implementation Reference

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

  Main handler function implementing complete table analysis by querying SQL Server system views for columns, primary keys, foreign keys, indexes, constraints, and general table information. Executes multiple queries in parallel and returns structured results.

  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)

  Input schema definition and tool registration for mcp_table_analysis within the MCP_MSQL_TOOLS array.

  {
    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/tools/index.ts:9-11 (registration)

  Re-export of the mcp_table_analysis handler from tableAnalysis module for use in the main tools index.

  export {
    mcp_table_analysis     // Complete table analysis
  } from './tableAnalysis.js';