get_procedure_schema

Instructions

Implementation Reference

• src/tools/get-procedure-schema.ts:5-74 (handler)

  Main handler function for get_procedure_schema. Queries INFORMATION_SCHEMA.PARAMETERS and INFORMATION_SCHEMA.ROUTINES to retrieve parameter definitions and routine definition for a stored procedure.

  export async function getProcedureSchema(
    db: DatabaseConnection,
    procedureName: string,
    schemaName: string = 'dbo'
  ): Promise<CallToolResult> {
    try {
      const pool = db.getPool()
  
      const query = `
        SELECT 
          p.PARAMETER_NAME,
          p.DATA_TYPE,
          p.PARAMETER_MODE,
          p.CHARACTER_MAXIMUM_LENGTH,
          p.NUMERIC_PRECISION,
          p.NUMERIC_SCALE,
          p.ORDINAL_POSITION,
          r.ROUTINE_DEFINITION
        FROM INFORMATION_SCHEMA.PARAMETERS p
        INNER JOIN INFORMATION_SCHEMA.ROUTINES r 
          ON p.SPECIFIC_NAME = r.SPECIFIC_NAME
        WHERE r.ROUTINE_NAME = @procedureName 
          AND r.ROUTINE_SCHEMA = @schemaName
          AND r.ROUTINE_TYPE = 'PROCEDURE'
        ORDER BY p.ORDINAL_POSITION
      `.trim()
  
      const request = pool.request()
  
      request.input('procedureName', procedureName)
      request.input('schemaName', schemaName)
  
      const result = await request.query(query)
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(
              {
                procedure: `${schemaName}.${procedureName}`,
                parameters: result.recordset.map((row) => ({
                  name: row.PARAMETER_NAME,
                  dataType: row.DATA_TYPE,
                  mode: row.PARAMETER_MODE,
                  maxLength: row.CHARACTER_MAXIMUM_LENGTH,
                  precision: row.NUMERIC_PRECISION,
                  scale: row.NUMERIC_SCALE,
                  position: row.ORDINAL_POSITION,
                })),
                definition: result.recordset[0]?.ROUTINE_DEFINITION || null,
              },
              null,
              2
            ),
          },
        ],
      }
    } catch (error) {
      return {
        content: [
          {
            type: 'text',
            text: `Erro: ${error instanceof Error ? error.message : 'Erro desconhecido'}`,
          },
        ],
        isError: true,
      }
    }
  }

• src/schemas.ts:45-48 (schema)

  Zod input validation schema for get_procedure_schema. Defines procedureName (required string) and schemaName (optional, defaults to 'dbo').

  export const getProcedureSchemaInput = z.object({
    procedureName: z.string().describe('Name of the stored procedure'),
    schemaName: z.string().default('dbo').describe('Schema name (default: dbo)'),
  })

• src/tools/index.ts:58-63 (registration)

  Tool registration in the tools list. Sets name to 'get_procedure_schema' with description and inputSchema using zodToJsonSchema conversion.

  {
    name: 'get_procedure_schema',
    description:
      'Gets the schema and parameters of a specific stored procedure',
    inputSchema: zodToJsonSchema(getProcedureSchemaInput),
  },

• src/services/SqlServerMCPService.ts:88-91 (registration)

  Handler registration in SqlServerMCPService.createHandlerMap(). Maps the 'get_procedure_schema' string to an async function that delegates to the getProcedureSchema function.

  handlers.set('get_procedure_schema', async (database, args) => {
    const { procedureName, schemaName } = args as GetProcedureSchemaInput
    return await getProcedureSchema(database, procedureName, schemaName)
  })

• src/tools/index.ts:19-19 (helper)

  Re-export of the getProcedureSchema function from the tools barrel module.

  export { getProcedureSchema } from './get-procedure-schema'