I'm a big fan of D&D, but I've got to admit the magic system leaves me wanting. Even though I usually play spellcasters, I find the spells to be overall a little too regimented and boring.
I've played some FATE and a small smattering of PBtA games, and though the magic systems are different than D&D, they still haven't scratched that mysterious itch.
So what I'm curious about is magic systems in other games. How does magic work in the TTRPGs you've played? Which systems do you really like? Which haven't worked for you?
I don't think I've encountered a magic system that ultimately hit all my wishes, but I can say I prefer the following:
1- self-contained spells
The idea that there are specific "magic recipes" that just work in a pre-determined way does "scratch my mysterious itch" more than flexible systems where you make up effects on the fly, it suggests that magic is just what it is, hidden laws of nature to discover and use to your advantage, but you're mostly bound to their rules and formulas. Changing some variables or boosting results is ok, but spellcasting as flexible as e.g. "you can control water", and then the player is allowed to make up any effect involving water they can think about (attack, defense, transportation, summoning...) on the fly based on some rules, is too open-ended for my tastes, and usually also either too powerful or too rules-complicated to prevent breaking it.
2- limited knowledge
As I like my role-playing to be about the
roles, I want characters with
limitations on what they can do, in order to have clearer roles for everyone. This is usually taken care by a combination of class-specific spells list and level-based amount of spells known.
3- permanent knowledge
While I'm mostly used to play with 'vancian' magic and daily preparation of spells, it is not my ideal. I would prefer that what magic a character can inherently do, would be the same every day, until they learn something new, in the same way that 'spontaneous casters' work.
With regards to how to limit spellcasting in a given time period, I am otherwise ok with spell slots for simplicity, no need for draining stats or rolling negative effects for spellcasting (I am also ok with games that have such things, but they obviously slow the game down and add more rules to manage).