MCPQL

Instructions

Implementation Reference

• src/tools/storedProcedureAnalysis.ts:8-93 (handler)

  Core handler function implementing mcp_sp_structure tool. Analyzes stored procedure by fetching info, parameters, dependencies, and source code using parallel SQL queries.

  export const mcp_sp_structure = async (args: { sp_name: string }): Promise<ToolResult<{
    info: any;
    parameters: any[];
    dependencies: any[];
    definition: any
  }>> => {
    const { sp_name } = args;
    console.log('Executing mcp_sp_structure with:', { sp_name });
  
    const qualified_name = normalizeSqlObjectName(sp_name);
  
    try {
      const pool = getPool();
  
      // Enhanced query for basic information - matching Python implementation
      const infoQuery = `
        SELECT 
            OBJECT_SCHEMA_NAME(o.object_id) as schema_name,
            o.name as procedure_name,
            o.create_date,
            o.modify_date,
            m.definition as source_code,
            o.type_desc,
            CAST(ep.value AS NVARCHAR(MAX)) AS description
        FROM sys.objects o
        INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
        LEFT JOIN sys.extended_properties ep ON o.object_id = ep.major_id AND ep.minor_id = 0 AND ep.name = 'MS_Description'
        WHERE o.type = 'P'
        AND o.object_id = OBJECT_ID(@qualified_name);
      `;
  
      // Enhanced query for parameters - matching Python implementation
      const paramsQuery = `
        SELECT 
            p.name as parameter_name,
            t.name as data_type,
            p.max_length,
            p.precision,
            p.scale,
            p.is_output,
            p.has_default_value,
            p.default_value,
            p.parameter_id
        FROM sys.parameters p
        INNER JOIN sys.types t ON p.system_type_id = t.system_type_id AND t.user_type_id = t.system_type_id
        WHERE p.object_id = OBJECT_ID(@qualified_name)
        ORDER BY p.parameter_id;
      `;
  
      // Enhanced query for dependencies - matching Python implementation
      const dependenciesQuery = `
        SELECT DISTINCT
            dep.referenced_schema_name,
            dep.referenced_entity_name,
            o.type_desc as referenced_type,
            dep.referenced_database_name,
            dep.referenced_server_name
        FROM sys.dm_sql_referenced_entities(@qualified_name, 'OBJECT') dep
        LEFT JOIN sys.objects o ON OBJECT_ID(ISNULL(dep.referenced_database_name + '.', '') + 
                                            ISNULL(dep.referenced_schema_name + '.', '') + 
                                            dep.referenced_entity_name) = o.object_id
        WHERE dep.referenced_id IS NOT NULL
        ORDER BY dep.referenced_schema_name, dep.referenced_entity_name;
      `;
  
      // Execute all queries in parallel
      const [infoResult, paramsResult, dependenciesResult] = await Promise.all([
        pool.request().input('qualified_name', qualified_name).query(infoQuery),
        pool.request().input('qualified_name', qualified_name).query(paramsQuery),
        pool.request().input('qualified_name', qualified_name).query(dependenciesQuery)
      ]);
  
      return {
        success: true,
        data: {
          info: infoResult.recordset[0] || null,
          parameters: paramsResult.recordset,
          dependencies: dependenciesResult.recordset,
          definition: infoResult.recordset[0]?.source_code || null
        }
      };
    } catch (error: any) {
      console.error(`Error in mcp_sp_structure for SP ${sp_name}: ${error.message}`);
      return { success: false, error: error.message };
    }
  };

• src/tools.ts:19-32 (registration)

  Tool registration entry in MCP_MSQL_TOOLS array, defining name, description, and JSON input schema for MCP protocol.

  {
    name: "mcp_sp_structure",
    description: "Analyze SQL Server stored procedure structure including parameters, dependencies, and source code",
    inputSchema: {
      type: "object",
      properties: {
        sp_name: {
          type: "string",
          description: "Fully qualified stored procedure name (schema.name), e.g. \"eco.usp_Insert_EconomicMovement_v2\""
        }
      },
      required: ["sp_name"]
    }
  },

• src/tools/storedProcedureAnalysis.ts:8-13 (schema)

  TypeScript type definitions for input (args: { sp_name: string }) and output (ToolResult<{info, parameters, dependencies, definition}>) providing schema validation.

  export const mcp_sp_structure = async (args: { sp_name: string }): Promise<ToolResult<{
    info: any;
    parameters: any[];
    dependencies: any[];
    definition: any
  }>> => {

• src/tools/index.ts:14-17 (registration)

  Re-export of the mcp_sp_structure handler from storedProcedureAnalysis module for barrel export.

  export {
    mcp_sp_structure,       // Complete stored procedure analysis
    mcp_get_sp_all_info_simple // Para compatibilidad con server.ts
  } from './storedProcedureAnalysis.js';