I literally only play Thief fan missions and on rare occasions play T2X.
These are the ones that I used to play or still play:
1) Heroes of Might & Magic II&III - II is my favorite but I used to play III a lot too - thousands of user maps across both games. The HOURS I spent playing them during my highschool and college..
2) Trials Fusion and Trials Rising - I have been playing user made tracks in recent years, they are great for short sessions.
3) Unreal - the most memorable project for me was Operation NaPali - a long campaign with notoriously bad voice acting, Nali companions, and cutscenes.
There were some ambitious projects that never saw the light of day, like The Chosen One - authors were promising RPG elements and vehicles.
4) New California (Fallout New Vegas) - a big single player campaign largely made by one guy released a few years ago.
5) Nihilum and 2027 (Deus Ex) - not long campaigns but worth playing for Deus Ex fans.
6) Portal 2 - a lot of great user maps but get too brain straining for me too soon.
I literally only play Thief fan missions and on rare occasions play T2X.
Diablo 1 - The Hell 2 Mod
HOMM 3 - Horns of Abyss + HDmod, with a lot of downloadable custom maps
Gothic 1 and 2 - a lot of different mods. The latest and the bigest for me was Archolos
Unreal 1 - Unreal Nighmare Mod
Legend of Grimrock 1 and 2 - there are really good custom campaigns
There are others, but I put here what I like the best.
Mods I've played.
"Ogle: The Return of Morcalavin" (Heretic 2)
"Enderal" (Skyrim). Only started it, got too time consuming
I also played many of these Quake 1 mods back in the day
I love mods just as much as gaming. And my favourite mod ever is Killap's Restoration Project for Fallout 2. Is so well integrated that changed my views on mods back in the day. Now there are a few that I recommend even for first time playthroughs. That's one.
Another incredible mod that is a world of it's own is the unnasuming "1.13" for Jagged Alliance 2. Elevates JA2 to the best real time tactics game ever made. I love it so much.