I recently played through SS2 again, largely for nostalgic reasons, and here's a list of the mods I used, in the order in which I installed them. (Install order matters because, SS2 not being designed for mods, they overwrite core game files; System Shock 2 Mod Manager is highly recommended for keeping everything straight.)
I won't link them because they should all be easily searchable, at most by adding "ss2 mod" to the mod name in your search query; if you can't find one, comment and I'll update with a link.
* Anomalies, Discrepancies, and Outright Bugs (ADaOB) v0.3.0 - bugfixes, &c.
* four hundred v?? - hi-res textures
* SHTUP Beta 6 - more texture improvements
* DeepFriedBeer's sound upgrade v3b - much improved game sound
* PSI-Amp V2 - High-poly/high-res texture replacement for the psionic amplifier weapon.
* Rebirth 02 - High-poly-count model replacements; goes very well with high-res texture packs.
* The following "Tacticool" weapon replacements:
- Assault rifle v1.0
- Grenade launcher v1.0
- Laser pistol v1.1
- Shotgun v1.0
- Wrench v1.0
- Pistol v.11b (ADaOB v0.2.8 compatible)
* Vurt's Hi-Res Space v1.1 - High-resolution skybox texture
I won't pretend that, even with these mods, the result is anything like a modern game in terms of looks. But it's a lot better than stock SS2, and if you're having a lot of trouble getting past the game's looks -- and that's worth doing, if you like your games to make you think and keep you on your toes -- then a setup like this one may help.
http://aaron-miller.me/misc/Aaron%27s%20SS2%20mod%20collecti...
The amazing thing is that the environment looks so good despite the graphical limitations of the time. A stellar job was done folding the starship interiors into the boxy limitations of the day's graphical tech.
Still, if you can look behind the graphics and can somehow relate to the time back then, it was also so different from Half-Life in many regards.