Deus Ex has a few great ones, Nihilum, 2027 & The Nameless Mod are all pretty good if you're looking for more imsim content

Half-Life of course has a pretty massive modding community, and they've made a lot of good stuff over the years - Echoes and Field Intensity for the 1st game and Entropy Zero 2 for the 2nd are basically on the level of proper expansion packs. Minerva, Mission Improbable, Uncertainty Principle and Downfall (all for HL2) are somewhat shorter, but still really good. There's also new campaigns for the Portal games, but I haven't gotten around to playing those yet.

Mount & Blade has a ton of total conversions, for various historical periods and fantasy settings

Medieval 2 was already mentioned, so some other cool mods for it are The Italian Wars and 1648, for the pike-and-shot fans among you

The Blizzard RTS games have a ton of custom campaigns, although I haven't played many yet. StarCraft 2 in particular has recently been having a ton of new campaigns made (although mostly in the format of overhauls of the existing campaigns, rather than being entirely new missions, but they can still be pretty extensive with the new units and mechanics)

MGS V has had a decent amount of modding done to it too - nothing too massive, since the modding tools are very limited, but Infinite Heaven allows you to tweak a whole bunch of stuff (and, in combination with some other mods, significantly lighten the grinding, which I probably wouldn't have been able to stomach the game without), and allows people to add new custom side-ops