SQL Server MCP Service

Instructions

Implementation Reference

• src/tools/get-function-schema.ts:5-81 (handler)

  Core handler function that executes SQL query to retrieve function schema, parameters, return type, and definition from SQL Server INFORMATION_SCHEMA.

  export async function getFunctionSchema(
    db: DatabaseConnection,
    functionName: 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,
          r.DATA_TYPE as RETURN_TYPE,
          CASE 
            WHEN r.DATA_TYPE = 'TABLE' THEN 'TABLE-VALUED'
            ELSE 'SCALAR'
          END AS FUNCTION_TYPE
        FROM INFORMATION_SCHEMA.PARAMETERS p
        INNER JOIN INFORMATION_SCHEMA.ROUTINES r 
          ON p.SPECIFIC_NAME = r.SPECIFIC_NAME
        WHERE r.ROUTINE_NAME = @functionName 
          AND r.ROUTINE_SCHEMA = @schemaName
          AND r.ROUTINE_TYPE = 'FUNCTION'
        ORDER BY p.ORDINAL_POSITION
      `.trim()
  
      const request = pool.request()
  
      request.input('functionName', functionName)
      request.input('schemaName', schemaName)
  
      const result = await request.query(query)
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(
              {
                function: `${schemaName}.${functionName}`,
                functionType: result.recordset[0]?.FUNCTION_TYPE || 'UNKNOWN',
                returnType: result.recordset[0]?.RETURN_TYPE || 'UNKNOWN',
                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:50-53 (schema)

  Zod input schema defining parameters for the get_function_schema tool: functionName (required) and schemaName (optional, defaults to 'dbo').

  export const getFunctionSchemaInput = z.object({
    functionName: z.string().describe('Name of the function'),
    schemaName: z.string().default('dbo').describe('Schema name (default: dbo)'),
  })

• src/services/SqlServerMCPService.ts:93-96 (registration)

  Registers the 'get_function_schema' tool handler in the MCP service's handler map, parsing args with the schema type and delegating to the core getFunctionSchema function.

  handlers.set('get_function_schema', async (database, args) => {
    const { functionName, schemaName } = args as GetFunctionSchemaInput
    return await getFunctionSchema(database, functionName, schemaName)
  })

• src/tools/index.ts:64-68 (registration)

  Tool metadata registration in toolsList(): name, description, and JSON schema for input, used by MCP service for listTools response.

  {
    name: 'get_function_schema',
    description: 'Gets the schema and parameters of a specific function',
    inputSchema: zodToJsonSchema(getFunctionSchemaInput),
  },