I really wish I'd gotten into the Silent Hill series because I sounds exactly like the kind of thing I'd love. Something I'd soil my pants over but still love, somewhere down there in the fear.
Resident Evil is always going to be a win, playing the original games now seems kind of embarassing to think those graphics ever scared you like they did but as it's already been said, the remake of RE1 rocks. Really, even after knowing the entire game from the original I don't know how many times I entered a new room, headr a noise or saw something move and just stood there for 10 minutes (quite literally 10 minutes), gun drawn, crapping myself. Eventually I'd move and maybe a super zombie would suddenly burst around the corner and start galloping in the general direction of my throat. Good times.
RE4, even though I love it to peices, isn't scary. One of the main reasons I did so well in it, concentrating on trying to get head shots and such forth takes away most of the scare. Well, most of it, the regenerators part of the facility scared me a lot. Anything that lose a head, both it's arms, part of it's chest and both of it's legs that can still find a way to crawl upto you and lunge at your throat earns a place in my nightmares.
Doom3, not much to say about it except that it scared me on quite a few occasions. Atmospheric lighting in that game really was done nicely but I admit sometimes it was just frustration. Especially that your marine couldn't understand the concept of holding a at least a pistol in one hand and a flash light in the other. One of my favourite scares in the game is with the disembodied voice of a women that beckons you over to a door where she start telling you about her baby. The fact that that door never opens makes the whole thing ten times more scary for me, some how I don't think I ever really want to know what would be inside.
FEAR, I don't care how cliche little girls with black hair are it still worked and since 'Ring' it'll work every God damn time. There are a few times that game really made me jump, and I mean jump, not just scare the crap out of me. I mostly took solice in the fact that she seems to spend most of her time going away from you. But take the ventilation shaft, when the lights go off and you see something that looks like a little girl spider walk it's way towards you I couldn't help just open fire until the lights came back on again. From a gameplay point of view I really liked FEAR, it was nice to have the 'slowmo' mode to explain why you were doing so well instead of just saying 'Oh, you're a one man army even though you're just a regular soldier.' The fact that you could also hear your enemies communications rocked so hard too, I'll be sad if I never see that used in another game.
Apart from those more well known titles I don't think I've actually played any really scary games, which is a shame... Eternal Darkness Sanity's Requiem I have played but I laughed more than crapped myself. I don't want to beat down too hardly on it but the game was just trying so hard to be a horror genre that it was failing.