Pretty decent list. Instead of listing my faves though like the others already have, I'm gonna do something different and list some mods that SHOULD have come with the game as DLC, they're just THAT good.
1. Mystcraft - If any of you have been paying attention to my posts, you'll know I bang on about this one a lot, but I do it only because I consider this pretty much an essential part of Minecraft now. So, still recommending it.
2. Ballistic Weapons - For UT2004. Adds a FUCKTON of incredibly awesomely done weapons and custom-built easily configurable mechanics to go along with them. This mod really showed off what the UE2.5 could do. And on top of that, there are even MORE community made add-ons for it that add even MORE awesomely done weapons. And the maps that come with it are pretty great as well. Seriously. Check it out.
3. Perfected Doom 3 - What Doom 3 definitely should have been. This overhauls almost everything in Doom 3 in some way, small or great. Enemies are now more interesting. Graphics have been massively upgraded and run even BETTER than stock. No more having to whip out the flashlight for every single little thing. The difficulty has gotten an overhaul (it's harder) and your weapons pack a LOT more punch and functionality to compensate. Oh and every weapon has at very least two kinds of fire, although 3 is definitely the norm. FFS, the BFG has FIVE firemodes now instead of just the one, each one being utterly amazing on top of that. So if you ever felt like returning to Doom 3, look no further. This is, without a doubt, the definitive version of Doom 3.
4. Oscuro's Oblivion Overhaul - Although it changes and adds other things, the main purpose of this mod is to delevel Oblivion almost, if not entirely. No more bandits walking around in stupid ass glass armor. No more lame loot. Honestly, I find Oblivion's leveling to be so bad that I think this mod is pretty much mandatory if you want to play Oblivion for anything more than just laughs. Having said that, it's pretty hard for beginners. Since a lot of enemies are now a deleveled threat, you'll be spending some time in the beginning just literally running away. Nevertheless, it's worth it and it makes each kill feel like a true victory.
5. Ziggy's Mod - Completely fixes the atrocious hand-holding of Far Cry 3. No more wall-hacks for you. No more super mini-map. No more brushing off bullets to the face. No more stupid AI. You wanna survive? You better learn some actual combat skills now, boyo, in true Far Cry 2 fashion. Although it's not all just removals at all. It also adds the essential and visceral ability to be able to takedown any enemy regardless of what they're doing or if they've noticed you. Now you may think this would be OP'd but not so. For when you do a takedown, you can still be easily shot. And getting shot at all is now much more of a problem. Also, the guns have been overhauled and given new attachments. Most of them have been buffed I believe. It also revamps the signature weapons, giving them more of a point. And finally, there's some quality-of-life improvements like removing the annoying humming in the menus. Now, whenever people talk to me about Far Cry 3, I can't help but ignore the base game entirely in favor of the Ziggy's Mod version