Here are a few that I like. I got all of them at the nexus.
Rivet City Maximum Resolutions
Maximum Res Megaton
Dirty Marlboro Reds
I'm sorry this shot is so small. But you can kinda get an idea of what some of the texture mods do.
And I recommend:
Darnified UI (more pc like hud and fonts)
More From Where That Came from (more oldies songs for GNR)
IPip (your own mp3's in the game)
Drama Radio (very cool old Space Patrol episodes)
Personal Vertibird (a vert you can call on command to sleep in or store items in. It can be used to fast travel in too, I believe. You can not fly it though)
I should go check out the newest stuff. I haven't been there in a while, so there may be even better textures and maybe even some good quest mods out now too.
So far I haven't used anything to alter the gameplay. No super weapons or armor, nothing like that. But if you are into to those kinds of mods, there are a ton of them.