With the no-fetch quest topic, I thought maybe we can frame this in a way so we can all think about what we would like, as well as what we don't like.
I would also maybe see if we can categorize them.
I'll start
My favourite quest was in Baldurs' Gate II, an investigation quest that made me search out someone who killed and skinned people.
I enjoyed it because I had to work out myself al the way to the end. It was challenging, it was multi-part, it wasn't a chore, and it was fairly unique among quests. It also played out differently based on how you had prepared yourself, meaning that it mattered to think about the quest and not follow from point A to B and just experience the ride.
As for the Categories that I can think of, there are.
Fetch Quests (steal priceless artifact by stealth, or go over there and just barge in and take his head; gather these resources so I can craft this magic weapon)
Straight up "go there and kill X"
Escort missions (NPC X must survive)
investigation missions (as per example)
Diplomacy missions (convince X to do Y)
Exploration quests (often seen as "please locate my probably dead relative")
Rumours are always true, which by the way, is a terribly lame trope. "It is said that at the top of that volcano there are treasures, but ach, it's probably just a rumour" nope. never is. you know that no story is ever false.
Puzzles and riddles and obstacle courses.
there are probably a few more, so feel free to suggest more, I'll add them to the list.