http://www.gamesas.com/index.php?/topic/1060141-mod-recommendations-for-new-players/page__p__15392814#entry15392814
http://www.gamesas.com/index.php?/topic/1103737-add-to-the-list-of-needed-graphics-mods/page__p__16150079__fromsearch__1entry16150079
http://www.gamesas.com/index.php?/topic/1155980-povuholos-tomorrowind-a-mod-recommendations-list-for-today/ - big section on graphic updates
http://www.gamesas.com/index.php?/topic/1159516-morrowind-graphics-extender-mge/
And no, there is not one mod that is a "big overhaul"
You gotta piece all the mods together to get everything, but it's a fun process for most of us because you can customize the game to look how you want it.
EDIT: And since you're using Steam, you have to redate the BSAs back to 2002 for some mods to work.
You'll need to use a third-party program to change the date. http://www.softpedia.com/get/System/File-Management/Attribute-Changer.shtml works great.
In the location where you installed Steam, look for "...Steam\SteamApps\common\morrowind" folder - The three BSAs will be located under the "Data Files" folder.
To get MGE to work, you'll have to disable Steam community.