get_procedure_schema

Retrieve the schema and parameter details for a specific stored procedure in SQL Server to understand its structure and required inputs.

Instructions

Gets the schema and parameters of a specific stored procedure

Input Schema

TableJSON Schema

Name	Required	Description	Default
`procedureName`	Yes	Name of the stored procedure
`schemaName`	No	Schema name (default: dbo)	dbo

Implementation Reference

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

Core handler function that executes the SQL query to retrieve procedure schema and parameters from INFORMATION_SCHEMA, formats and returns as CallToolResult.

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 schema defining parameters for the get_procedure_schema tool.

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 toolsList() function, providing name, description, and input schema for MCP tool listing.

{
  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, mapping tool name to the getProcedureSchema function call.

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

src/schemas.ts:82-82 (schema)

TypeScript type inference for the input schema.

export type GetProcedureSchemaInput = z.infer<typeof getProcedureSchemaInput>

SQL Server MCP Service