currently i am playing skyrim with more than 100 mods...
i think even more when i was playing oblivion... i remember i even had morrowind with all the npc and quests on my oblivion, had a completely overhauled spell creation mechanic and better developed batter (cuz battle in oblivion was moronic)
when i played morrowind, i had more mods than normal content, balmora was almost 5 times bigger...
also Unreal tournament, millions of mutators, maps, weapons, skins and game modes...
I love mods that are done well. I'm never a fan of extra weapons and spells so much, but extra missions / worlds / areas are all good. It's one of the biggest things I'd like for Borderlands 2 - a kit that would allow users to create their own game areas and missions.
As long as there's a very well functioning system of sorting out the very cream of the crop, it can be good. If I have to wade through all the shit to find gold, I'll never bother.