>Absolute favorite, from a video game:
Tyranny did this great shit with combining sigils, accents, enhancements, and expressions and modifiers that were limited by different lore requirements and recovering the sigils to start with.
Unfortunately it's way too complicated to be in a TTRPG without bogging the game down, and everyone just ended up as a mage, anyway, since they're shared sigils.
They also made a lot of neat puzzles for unlocking magic doors for the main spire towers in the game that I 100% shamelessly stole for puzzles when I was DMing. Pick up pieces of the correct answer while trying to avoid the big boss stomping around the main halls, and leave them wondering if they got them all. They really liked it, but I didn't dare use it more than twice, for two similar types of dungeons.

>As far as tabletop:
Mage: The Ascension/Awakening's whole "it only works if people believe it while you do it" is a real nice way to fluff up your magic and make it limited in a fun roleplaying way.