I think the spells themselves have a lot to do with making magic interesting.
"Magical" spells:
* sometimes have limitations (only at night, only works on certain targets, requires a rare component that might have nothing obvious to do with the spell) suited to the theme of the class
Poster child: Entangle
* sometimes have serious consequences for the caster or his allies (insanity, aging, calling unwanted attention to the party)
Poster child: ADD's Contact Other Plane
* sometimes combine more than one effect
Poster child: Monte Cook's "Ride the lightning" from Arcana Unearthed, combines LB and D-door.
* imitate mythological ideas
Poster child: Pathfinder's "Beguiling Gift" (witch spell, target accepts and immediately uses an item). Another: ADD's "Binding".
* have their effects described in somewhat vague terms, so that inventive players can use them in numerous situations
Poster child: ADD's "Command", Stone Shape
* have serious implications for the game world
Poster child: 3E's "Awaken"
Now, I'm not saying every spell in the book should be like this. There's plenty of room for "bread-and-butter" spells too. But just because there is a spell to increase Strength, doesn't mean there has to be a spell for every attribute, plus higher-level versions to increase the attributes for longer periods or for multiple targets. Maybe no one's figured out a spell to increase Con yet, or if they have it causes warts to sprout all over the target's body.
When a class's spell list contains many spells that meet one more more of the above criteria, then I find myself wanting to play that class so I can use those spells.
When most of the spells provide clearly laid-out mechanical benefits, have narrowly defined uses, and magic has no appreciable consequences for its users, I find myself playing fighters and thieves instead.
Ideally, the spell list should imply that magic-users do not fully understand the source of their power, because the spell list
doesn't cover every need and because there are still undesired consequences to using magic. On the other hand, spells that are tricky to use in some way should be a little more powerful than the "bread-and-butter" spells.
Ben