If you want to scream "OMFG I'm going to DIIIIIIIIIEEEEEEEEEE!!!" for hours on end, look no further than the original AvP.
Other than that, I've played a bunch of good horror games but nothing that jumped out at me as scary. The original Condemned was probably the closest I've played in the past few years. Too bad the second game sucked.
Or check out my new webcomic: http://selfcentent.com/