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.