There's been a few times in the past when I would like to mention and recommend various mods that I enjoy playing, however, posting in that games thread only to what kind of amounts to an advertisement seems a bit rude so here this can be done with impunity.
Please list the mod along with the game (and specific version if necessary) as well as a link to where it can be downloaded and a brief synopsis of the mod.
I've got several that I'll start off with...
Sins of a Solar Empire: Rebellion
What I would consider to be the best mod available for Sins of a Solar Empire. This Star Trek mod is a total conversion that adds the new factions of the Federation, Klingon Empire, Romulan Star Empire, Cardassian Union/Dominion and Borg. Ship, building, research and overall detail is really nice with a very close attention to detail. (http://www.moddb.com/mods/star-trek-armada-3)
Interregnum is a Star Wars mod that focuses on adding the factions of the Empire and Rebel Alliance alongside the base factions of Sins of a Solar Empire. It also includes several mods done by the developer that add things like stations in orbit you can capture, derelict ships and changes the factions to have greater difference between them (http://www.moddb.com/mods/star-wars-interregnum)
Where Interregnum focuses on including Star Wars factions alongside the base ones. Sins of a Galactic Empire only has Star Wars factions and has a very different balance system. Capital ships are the mainstay of fleets with frigates being quickly produced support. Shields mean a lot more with ships that lose shields getting destroyed very quickly. It captures the scale of Star Wars conflicts, the huge armaments on ships like ISDs and the SSD and the vast numbers of star fighters. It also includes more factions; Galactic Empire, Rebel Alliance, New Republic, Imperial Remnant, Republic, CIS as well as a small add on for an alpha build that includes the Sith Empire and Old Republic from The "Knight of the Old Republic" series. (http://www.moddb.com/mods/sins-of-a-galactic-empire)
This mod is made by the same people who made the "Thrawns Revenge" mod for "Empire at War". While the mod is in alpha I think a 1.0 release is planned within the next month or so. The model work is very nicely done and the gameplay is changed quite a bit with more focus on how the Rebels can manourver around the Empire and the Empire keeping control through the presence of their capital ships. Supply is done differently as well, with energy now being star fighter count that requires resupplying from docks and supply ships. (http://www.moddb.com/mods/star-wars-ascendancy)
Supreme Commander: Forged Alliance
I like a mod that just adds more things to blow up and this mod does just that. Adding new units to the UEF and Cybran, as well as some to Aeon and Serephim, this mod includes many mechs and some silly units like a base defence gun that's four three barrelled naval guns on a single tower. I believe several of the mechs are actually versions from the Battletech universe so if you like Madcats you can have armies of them duking it out. I cannot say how balanced the mod is for multiplayer and the mod doesn't seem to be being developed any more. (http://www.moddb.com/mods/total-mayhem1)
X3: Albion Prelude
I must confess. I created this topic so I could specifically mention this mod. I don't even own X3: Albion Prelude but that will probably change considering how this mod looks. Adding in ships from Star Wars, from X-Wings to the YT-1300f light freighter to Star Destroyers and Mon Calamari Cruisers the amount of work and detail that's gone into the models in the mod already is really impressive for a one man team. It features cockpits for various ships as well as what I believe to be functioning R2 units for X-Wings (You might have to buy them to gain the benefit of them, showing up in your ship if you have one). The developer is even including planets from Star Wars like Hoth and Jakka from Episode 7 as well as debris fields. This mod seems to have huge potential given the rate at which the developer is working on it. (http://forum.egosoft.com/viewtopic.php?t=368892&postdays=0&postorder=asc&start=0)
Here's a video from Captain Shack from XPGamers that show cases some gameplay from the mod. (https://www.youtube.com/watch?v=TxAZx8M2OiE)
Turns the game in to "Grimdark mass-combat simulator". Basically, the best 40k game you'll come across.
http://www.moddb.com/mods/ultimate-apocalypse-mod
Star Wars Knights of The Old Republic II: The Sith Lords
The Famous Restored Content mod, adds tons of content to an already great game. Be wary of crashes however.
http://www.moddb.com/mods/the-sith-lords-restored-content-mod-tslrcm
(also on steam)
a few oldies but still amazing goodies
Mount&Blade
My whole reason to still play the old Mount&Blade even if i own Warband, it's a fantastic mod that manage to immerse the player into those western movies atmosphere. Must play if you ever liked watching some good western.
https://forums.taleworlds.com/index.php/board,150.0.html
Thief 2 The Metal Age
A complete whole new campaign with quality voices and missions, could have easily been a commercial expansion to the original Thief 2, it's just fantastic. There's a HD retexture mod available (http://www.moddb.com/mods/thief-2x-hd-texture-mod) for it.
http://www.thief2x.com/
Ghost Recon
Gigantic amount of content in that mega-unofficial expansion of that good old Ghost Recon, more of those very replayable missions than you'll ever be able to play.
http://www.moddb.com/mods/heroes-unleashed
XCOM: Enemy Within
Makes the game much more strategic, adds tons of new content, makes the game much longer. Has a huge number of options, including some that help cut back on the added time the mod takes to win at.
http://www.nexusmods.com/xcom/mods/88/ (http://www.nexusmods.com/xcom/mods/88/)
Torchlight II
Adds a huge amount of new content, including essentially an entire extra section to the game at the end and a couple of new classes to play as.
Found in the steam workshop.
Almost a total conversion. Skill trees for every class are redesigned, new items, new runewords, new unique items, new sets. New mobs, new mob distribution, new maps. Challenges ("uberquests") with special rewards involving the new maps. Full documentation for every change... almost: some of the very, very end game stuff is considered "secret", but this only consists of maybe three or four uberquests, out of dozens. Integrated with PlugY if playing single player, also has a closed realm multiplayer option.
About the only thing that doesn't change are the main progression of act quests (though some are shortened to make space for more uberquests by removing the related zones) and the act bosses, and even the act bosses have new abilities.
Edit: Oh yes, and mob density and overall pacing was increased. Combined with the new skills, this makes gameplay somewhere between "this is ludicrous" and "almost enough dakka."
forum.median-xl.com (http://forum.median-xl.com)
Civilizations IV: Beyond the Sword
Completely re-configures the era and tech progression, taking going from pre-agrarian nigh-caveman hunter/gatherers into post-modern future. At the earliest start, game year is 50,000 BC. Adds tons of new techs, tons and tons of new buildings (including wonders), new units, new resources, new religions, unique benefits between religions. Adds actual cultures, acting as a kind of world wonder, which grant the civ that build them unique heroes that can act as strong, special military units or can be settled as great people.
Might actually go a little too far. Trying to actually play a game from caveman to cosmos can easily take a hundred hours, and that's not even accounting for late-game turn slowdowns.
CivFanatics thread and download. (http://forums.civfanatics.com/showthread.php?t=288570)
Warband multiplayer in a persistent world, has included a campaign and clans for some time. On the decline, but a historically popular Warband mod.
Two similar Warband mods that transorm Calradia into Medieval Europe and the Near East. Very good.
The classic Warband SP mod, adds many features. Pretty vanilla.
Now an addon known as Napoleonic Wars (NW), but historically the most popular mod for Warband. Spawned an incredible following and incredible new features and combat... Clans styles as Regiments are prevalent.
Only persistent by session, but there's a bank to hold money between sessions. Middling popularity, loyal fanbase, and has spawned a number of spinoff Persistent X mods.
Impressive fantasy SP mod. A total conversion with new mechanics, fun to play, slow paced.
a complete revamp for vanilla.
It's a glorious clusterfuck of factions, warfare, and death. A little dated.
update of 1866, great duelling and sharpshooting--most fun for a small group of players. Really nails the old west... There's waning support, but one of my personal favorites.
Really fun!!! There are a number of assassins that blend in with the population, hunt each other in MP.
The original Deluge mod became With Fire and Sword, this is an updated version of that mod. Historically cool. A small, but adequate player base. VERY VERY VERY slow paced in terms of combat, both literally and strategically. Unfortunately, servers rarely have the teamwork necessary to achieve the resounding success of the earlier mod's player base.
A fucking amazing wave-survival mod. There are dozens of factions for both defenders and attackers. You NEED this mod if you're a warband player. Check it out.
Sadly discontinued, a well-designed mod set in pre-napoleonic Europe. Check it. Cannons!!!
Changes Calradia to Feudal Japan. Great mod, well designed. SP
A graphically impressive Napoleonic. Fairly overhyped, can be fun.
In addition to the above there is a long history of Roman mods that have succeeded eachother and are MP only, mostly event-based, and heavy on teamwork. Check out whichever one is the current one!
A few other's I haven't touched on. M&B: Warband's modding community it by far the best I've seen in any game in terms of quality and quantity of content. Not to mention inventiveness.
Dwarf Fortress:
X3: Albion Prelude
Litcube's Universe
X3 Rebalance Mod (XRM)
DOOM (technically DOOM II but whatever)
Oldies but goodies, the Memento Mori WADs are stupidly hard. Seriously, they're designed to screw with you, and I'm fairly sure that at least a few maps can be made unbeatable if you make a mistake. It's fun though, and spices things up with some new gothic textures and decorations. I play these when I'm bored with the vanilla game, but still want something similar in look and feel.
Memento Mori (http://doom.wikia.com/wiki/Memento_Mori)
Memento Mori II (http://doom.wikia.com/wiki/Memento_Mori_II)
A Batman-themed DOOM level set. Extremely well done, with all new graphics and even a hub level based on the Batcave! Very temperamental on modern source ports, I recommend running it with Chocolate Doom.
Batman DOOM (http://doom.wikia.com/wiki/Batman_Doom)
WWF No Mercy
only playable through emulators, like all the mods available for that game as the real hardware can't run mods, this is a gigantic piece of work, new animations, new features, new arenas and near to 200 wrestlers of every eras/countries in the latest beta version! The most comprehensive mod i ever saw on a wrestling game.
http://s4.zetaboards.com/Old_Skool_Reunion/topic/10005574/1/.
OpenXcom
A huge mod for openxcom that transform the classic human vs aliens situation into a bizarre mix of pirates antics, crazy amazons and insane cults, it's very fun to play while keeping the difficulty of the original.
Boltgun is maintaining a thread (http://www.bay12forums.com/smf/index.php?topic=151935.0) that is just brillant about his campaign in this mod.
http://www.openxcom.com/mod/x-piratez
Unreal Tournament
An incredible total conversion that transformed the good old arena shooter into a rather deep infantry warsim , modifying everything from the physics, the gameplay, the visuals , the mechanics, you couldn't recognize the engine. Infiltration introduced many concepts that were later found in more modern infantry games.
"As real as it gets"
http://infiltration.sentrystudios.net/
One of, if not the most expansive mods for RTW, changes pretty much every aspect of the game and adds a historical bent to the game while improving gameplay on all fronts.
http://europabarbarorum.com/EB1/
Medieval 2: Total War
Though still not quite up to the level of EB1 in a lot of ways, the recent autumn release is evidence of it being fully capable of standing on its own as a great mod.
http://www.europabarbarorum.com/
And anyway, you're Neonivek. It's well known your proclivities don't match popular opinion.
Ok, I admit... as soon as I saw "You're Neonivek" I was expecting something like "You hate everything and are a loser" :P So thank you for not going there.
But naw my issue is mostly that every mod has to be a special snowflake mod and doesn't play nice with the main game.
But with these many mods they can't all be that.
I mean... Most mods are this... If the game had a pistol, the mod also has a pistol but it is flat out better...
If the Modder feels like it that pistol might take say... 5% more effort or heck 2x the effort to obtain but it is so egregiously better it overshadows the original content.
If the mod is supposed to add the next level of difficulty with gods and demigods, then yeah of course stuff would be better. Or if it is a setting shift that also justifies things... OR if it just entirely changes the progression of the game (The Long War for example does that)
Solid and Shade. A dark take on M&B, with some necromancy focused troops, and quite a bit of lore to go with it.
https://forums.taleworlds.com/index.php?topic=100545.0 (https://forums.taleworlds.com/index.php?topic=100545.0)
It's a glorious gore-fest for Doom that makes shooting stuff way more satisfying. Additionally it drastically modifies weapons, melee combat, enemy abilities and weapons to make it faster paced, more lethal and more varied. The violence and action is basically dialed up to an extent that makes it all look really ridiculous and over-the-top. And I love it. (http://www.moddb.com/mods/brutal-doom)
Here's a trailer for the recently released version 20b. (https://www.youtube.com/watch?v=NW8AHt5dgpw)
Available here (http://forums.steampowered.com/forums/showthread.php?t=2855200).
The official English translation of the game was really, really bad. In a game that(by design) relies on the text for you to figure out what is going on after you are thrown in with no memories, having to deal with the tortured grammar and poor word choices made it a struggle. It also caused the UI to be even more confusing and clunky than it needed to be. The mod fixed that and made this into a really interesting game.
Also grab the Extended Actions mod on the second post from the above link, which adds a bunch of controls that for whatever reason weren't officially included in the game.
Total conversion for Deus Ex set on a forum. Completely voice acted (competently, too), about the same length as the original game. If you can get over weird inside jokes (which taper off after the first hour or two anyway), I'd absolutely recommend this as it's basically a full game.
Another total conversion for Deus Ex, this one a prequel of sorts. Through what I can only assume to be black magic, a team of Russian modders somehow managed to make Deus Ex look and sound incredibly good. Story is pretty good, dialogue/writing is of variable quality, but I can't tell how much of that is because of the quality of the original work and how much is because of sloppy translations.
Brutal Doom (http://www.moddb.com/mods/brutal-doom)
Crank everything on 12 out of 10, balanced weapons, new moves for enemies, tons of gore, you can play with a somewhat vanilla mode if you only want the gore.
Demonsteele (http://www.moddb.com/mods/demonsteele)
Turns Doom into a nervous hack and slash with a new health system.
Both are compatible with most wads.