SQL Server MCP Service

Instructions

Implementation Reference

• src/tools/list-functions.ts:5-74 (handler)

  Core handler function that executes SQL query to list SQL Server functions (scalar and table-valued), filters by schema and type, and returns formatted JSON result.

  export async function listFunctions(
    db: DatabaseConnection,
    schemaName?: string,
    functionType?: 'SCALAR' | 'TABLE'
  ): Promise<CallToolResult> {
    try {
      const pool = db.getPool()
  
      let query = `
        SELECT 
          ROUTINE_SCHEMA,
          ROUTINE_NAME,
          ROUTINE_TYPE,
          DATA_TYPE,
          CREATED,
          LAST_ALTERED,
          CASE 
            WHEN DATA_TYPE = 'TABLE' THEN 'TABLE-VALUED'
            ELSE 'SCALAR'
          END AS FUNCTION_TYPE
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_TYPE = 'FUNCTION'
      `.trim()
  
      const request = pool.request()
  
      if (schemaName) {
        query += ' AND ROUTINE_SCHEMA = @schemaName'
        request.input('schemaName', schemaName)
      }
  
      if (functionType) {
        if (functionType === 'SCALAR') {
          query += " AND DATA_TYPE != 'TABLE'"
        } else if (functionType === 'TABLE') {
          query += " AND DATA_TYPE = 'TABLE'"
        }
      }
  
      query += ' ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME'
  
      const result = await request.query(query)
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(
              {
                functions: result.recordset,
                count: result.recordset.length,
              },
              null,
              2
            ),
          },
        ],
      }
    } catch (error) {
      return {
        content: [
          {
            type: 'text',
            text: `Erro: ${error instanceof Error ? error.message : 'Erro desconhecido'}`,
          },
        ],
        isError: true,
      }
    }
  }

• src/schemas.ts:37-43 (schema)

  Zod input schema for the list_functions tool, defining optional schemaName and functionType parameters.

  export const listFunctionsInput = z.object({
    schemaName: z.string().optional().describe('Schema name to filter functions'),
    functionType: z
      .enum(['SCALAR', 'TABLE'])
      .optional()
      .describe('Type of function to filter'),
  })

• src/tools/index.ts:53-57 (registration)

  Tool registration in toolsList(): defines name, description, and inputSchema for list_functions.

    name: 'list_functions',
    description:
      'Lists all functions (scalar and table-valued) in the database',
    inputSchema: zodToJsonSchema(listFunctionsInput),
  },

• src/services/SqlServerMCPService.ts:103-106 (registration)

  MCP service handler registration: maps 'list_functions' to a wrapper that calls the core listFunctions function.

  handlers.set('list_functions', async (database, args) => {
    const { schemaName, functionType } = args as ListFunctionsInput
    return await listFunctions(database, schemaName, functionType)
  })

• src/schemas.ts:81-81 (schema)

  TypeScript type definition inferred from listFunctionsInput Zod schema.

  export type ListFunctionsInput = z.infer<typeof listFunctionsInput>