I've enjoyed Nightmare House 2 for Half-Life 2.
I never finished Afraid of Monsters, but it was pretty good.
I wasn't terribly fond of They Hunger, but a lot of people enjoy it.
Paranoia for Half-Life was pretty damn good. A bit short though.

Theres a ton of them for Command & Conquer Generals, Homeworld 2, Dawn of War, Company of Heroes, the list goes on.

There are a few for Baldur's Gate 2, though imo the only good one is Classic Adventures

Desert Combat and Forgotten Hope for BF1942
Kungfu Mod for Max Payne
Third Age for Medieval 2 Total War

I'll add more once I think of more.

Sorry I missed the single player bit. But yeah the Alien swarm mod is somewhat different from the remake. It can be played single player but it's darn hard. Been a while since I played it, but it had more of a Space Hulk like vibe about it.You still control your entire squad of 4, but only 1 at a time of course, so it becomes a game about moving ahead one unit at a time while placing the other 3 to cover your back and any side branching paths while you're not directly controlling them.
In level 2 there's a tricky section with a rather large open area where aliens keep swarming into. Only managed to get all my units to the elevator once, darn unsporting moves to have eggs hatching as the elevator reaches ground floor.

If you're into RTS games, then Command&Conquer: Tiberian Dawn Redux for C&C Generals Zero Hour is a solid choice. It implements the first C&C game into General's engine. If you've played with the original and liked it, it's great for nostalgia, and if you haven't, Tiberian Dawn is still a good game, especially with the modern features of Generals.

Totally released? The BSG mod for Homeworld 2, Fleet Commander, has beta versions released that are a lot of fun, but the game is far from completely released yet.

Blue Planet is 2/3 released (three episode story) and while it is set in the Freespace 2 universe, it is set about 20 years after Freespace 2 and thus is practically a total conversion mod for Freespace 2 though there is a small overlap in game models. The second episode, Blue Planet: War in Heaven part 1, has to rank with some of the best single-player games I've played, never mind best mod. Some of the ones that don't use any of the Freespace 2 game assets and don't actually require Freespace 2 to play since the engine is open source have gotten good reviews like the Babylon project, but I haven't tried them. Beyond the Red Line and Diaspora, the BSG Freespace 2 mods, aren't fully released.

This thread should be active.

So... ACT-E-VATE!
That said, I have nothing to add.

I just want other people to say stuff.

Oh right, check out Thief 2X:

http://www.thief2x.com/

I haven't tried it yet but I heard it's excellent.

The best single player total conversion mod I remember ever having played is They Hunger for Half Life. I was a kid back then and never finished it because I was so scared.

Now I'm kind of lusting after Out of Hell for UT2004. Maybe I'll try it as soon as today.

Beat me to it ya bam pot