Trevor Gruen said:
The only 2 games I can think of personally that allow for awesome mages is Ziggurat and Lichdom Battlemage (and I haven't played the latter).
Lichdom is quite good. I haven't played Ziggurat, so I can't compare but Lichdom is sort of Dark Messiah (it's first person and vaguely looks similar) and Diablo (lootz). The game is rather linear with an occasional optional offshoot for a small arena battle. At any rate, you go around blasting enemies with some pretty impressive powers and you get spell components (think loot. Because it is. They are separated into white, green, blue, purple, orange, etc in terms of rarity, as well, and have levels on top of that), in order to craft more and better spells.
To be honest, the crafting is a bit of a burden - you get A LOT of spell components and most of them you won't even use. But you'll have to in order to improve the other components. And that's because your inventory space is limited (well, to about 200 items but it does fill up fast) and you need to make room for more. Which is crap in its own way, since you're interested in the rarest highest level loot you can get - levels are just progressive: at some point you start getting level X+1 components instead of level X. Once that happens in a while you can stop craft your new spells with the new level components and not bother until the next time you start getting one level higher loot. Also, the crafting is a bit complex...even though it shouldn't be. It's not hard to grasp, really, as thankfully the tutorials they have serve a good purpose, however, you do have to read up on it, instead of playing. All in all, I found the crafting was stopping the game instead of letting you play.
Still, I do recommend the game. It is pretty good and crafting is really my only complaint. And it's a minor one, really, even though I went on for a paragraph on it.
Other than that, there is Magicka. It is, hands down, the best spell system I've played. You build your spells on the fly and this leads to some pretty impressive results. And some pretty impressive failures, too. Especially if there are other mages around (like, for example, other people you can play with).
In addition, I can recommend Divinity: Original Sin - magic is quite impactful there, as pretty much every spell interracts with the environment in some way - rain can douse fires and makes people wet which you can follow up with a cold spell to freeze them completely solid; lightning not only damages and stuns people, but it also gets conducted through water, so you can damage and stun whole groups of people if they stand in water (or blood) and rain can have them stay in water; you can also apply fire to the water (like a fireball) and it gets evaporated into a mist which obscures vision (great to have between you and enemy ranged combatants) and you can
also electrify to turn into a cloud that stuns anything that crosses through. And so on and so forth. Combat with mages involved usually ends with the terrain being left burning, wet, smoking, and generally "like somebody has thrown magic around". Do be careful with friendly fire, though.