I don't have much to contribute to this that hasn't already been mentioned, so I'll be really brief and say that the original Alone in the Dark, Clock Tower and Fatal Frame (only played Crimson Butterfly) were pretty damn creepy. Hearing the scissors get closer and closer to you in Clock Tower induced panic like no other game. Alone in the Dark was just overall creepy in a Lovecraftian way. It actually got Pavlovian at some parts, where the music would just randomly change and you figure that something's coming after you. Sometimes, it's just random and not actually indicative of anything, though. And Fatal Frame... well... do I really have to explain this?
Another game which I don't think has been mentioned yet is one of my all-time favourite Sierra adventures, The Colonel's Bequest. Especially when playing on my old monochrome PC when I was 10, there were times when I actually turned it off and had to stop playing it. It wasn't scary in the survival-horror sense, but rather psychologically. Highly inspired by And Then There Were None by Agatha Christie, dead bodies of murdered characters start showing up, and it really keeps you on edge, not knowing who the killer is. Sometimes, you'll be in a room, sleuthing around, and you'll see the shadow of somebody walking outside the window, and it terrified me. One other part in particular that stands out is if you find the hidden basement in the mansion, and discover that shortly after you found their bodies, the killer took the corpses and dumped them down the laundry chute to hide them. You walk through the dark basement with a lantern and find a pile of all the dead bodies that you found throughout the game. Creepy shit.