Ultima 8 - Despite its various flaws, it's multiple ritual and item based magic systems was very cool. It had 4 completely different kinds of magic, not one-size-fit-all schematic like most games. Necromancy deals with Earth and Death, Thaumaturgy with the Astral sphere, Theurgy with Air and Life and Sorcery with Chaos and Fire, each school having a good amount of unique spells.
Necromancy - You research how to cast spells in-game by reading books or speaking to old necromancers. Once you know the necessary ingredients, you gather or buy them, place them in a bag, cast an incantation and then receive a token that you can use to cast the spell at will. The token will disappear after a spell is cast, requiring you to prepare spells ahead of time, or carry all sorts of reagents around.
Thaumaturgy - You purchase mystical spellbooks outlining the incantation and the reagents for the spell you need, gather them, charge up the book with the reagents, and then use the book to invoke the spell. Once the spell is cast the book must be recharged. Only once charge can be held at once.
Theurgy - You shape symbols for your spell, one symbol for each spell, from silver you mine. Then you bless the symbols. Now each symbol can cast a spell as often as you want, provided you have enough energy to do so.
Sorcery - In-game, in a book, you research how to use a pentacle: you learn which part of the pentagrammon has which name, and for every spell, you learn, still in-game, what reagents to place at what parts of the pentragram, along with where to place candles of ash or blood, depending on the spell. Then you put a magic focus into the center, light the candles, cast the spell and charge up the focus with a certain amount of spell charges. The higher quality the focus used, the more often it will be able to cast a spell, in exchange for spell points. Once it is exhausted you must recharge new foci.
Clerics prayed for spells in advance, wizards spent mana (better yet, stamina and health), druids to gather reagents, sages to combine runes, etc. This system required you to actually learn things in-game, memorize things like a student (Where does the red candle go again to prepare a Conflagration spell? Did I forget to bring Sulphurous Ash?) and reward you with a sense of accomplishment for becoming proficient in all schools. In U8, magic was extremely mysterious, unwieldy and arcane. And its use was usually reserved for some big event that was necessary to get past an obstacle, rather than just spamming fireballs.
The Trilogy of the North (The Realms of Arkania series) - Magic was pretty common, but... still, 85% of the spells were non combat-y utility spells. And that was good. Many spells were only accessible at high levels, and to finally cast the first teleportation spell is a milestone. Also, the high mana cost of offensive spells and the non-guaranteed regen during rest meant that a mage could be out of mana for days if he wasted it on demonstrations of power. The thing about RoA is that they implement all of the skills and spells from the PnP system, but their usefulness for a CRPG varies widely. Some are essential, some are only good for earning a little money here and there, some are used once or twice throughout the whole trilogy, and some are just flavor. Take a look at a guide before making your party to see what's what. But otherwise they're great games, and have some very creative spells.
Almraiven and Shadewood modules for NWN - Mixing regents, brewing potions, book reading, ritual performing... makes you really feel like a mage, as opposed to just 'slinging spells around'. You even get your own laboratory and it's pretty well written too. Also other low level NWN 1/2 modules or RP servers. Fireballs are a sight that an adventurer almost never sees. Only the best manage to ever reach level 5, say. Magic becomes special and all the more appreciated this way.
Darklands. Sure some of them were the whizz-bang kill variety but you were still limited by how fast you could throw them. The alchemical formulas and especially the components are expensive and usually involve some traveling to get everything you need. Skill requirements are also pretty high for the deadly ones requiring you to spend even more on tutors. The developers were also clever enough to give several of them uses outside of combat. Potions and prayers were also severely limited and could be used as additional dialogue options.
Aleshar: World of Ice - You can’t buy spells, learn them from scrolls, you don’t have mana... none of that crap. The first time you try magic will probably cause your heart to explode. As will the second and third time. With nobody to teach you magic, you need to learn how to manage it yourself. It's one of the few games where you really have to consider whether to use magic or not – even on the very highest levels, you can overexert yourself, causing instant (or slightly delayed) death.
Infra Arcana has a pretty great system: casting a spell increases insanity, which can have severe gameplay consequences if the value rises over a certain threshold. Casting also costs mana, which is quite limited—you cannot generally rely on casting a bunch of attack spells to destroy all of your enemies, for instance. I've found that the most useful spells are non-attack spells like clairvoyance and teleport, which is also a feature that is somewhat unusual for an RPG. Even a high-level caster will generally not be able to rely on magic to destroy the later-level enemies, though power does increase significantly as caster level increases, if the player makes wise decisions at level up. This system probably works especially well because IA is such a good and difficult game, where you actually have to think about each of your decisions, and the consequence of a bad action is generally PC death.
Betrayal at Krondor. Unusual casting system that uses life force and very unique, powerful spells...no mana, no memorizing. Basically you can choose how powerful your spell is, but it costs caster's stamina - the stronger the spell, the more tiresome it is. When you're out of stamina, you start losing health if you continue casting, and this leads to crippling your stats, like strenghth and speed, melee accuracy, etc.
Ultima Underworld - you collected runes and found scribbled notes on dead skeletons of rune combinations they know of. Then you arrange your runes in order and cast your spell. It sets up magic into neat components, then not only tasks you to find those components (runestones), which is a small reward in itself, but then gives you a surprising amount of freedom to experiment with those components. You can even learn a spell or two that aren't documented anywhere.
Dragon Wars used magic for puzzle solving.
Elder Scrolls games prior to Oblivion was much better and let you be more creative pulling off crazy shit.
Arx Fatalis - you have to draw runes in a certain order to perform various types of spells. When you get new runes you can try to make new spells.
Quest for Glory games and Heroine Quest are great when it comes to solving problems with magic (point & click adventures with some RPG elements)
Dominions - No magic systems can beat one where you have to harvest and sacrifice virgins to summon devils.