If you like scary, you absolutely need to play Thief 3. The shadowbridge cradle (correct?, also known as the insane asylum from above post) was the most scary I and a lot of other people have ever played. I've played D3 too but honestly, only the first three levels or so are scary. You start to get into the "yeah lets blast some more demons" further in the game and they don't surprise you as much as they used to. (the imp in the second level jumping at you through those stairs
)
Also from reliable sources I know that System Shock 2 is widely regarded as a scary game, haven't played it yet though. I found Ravenholm to be an irritating but not scary part. Those ubahr zombies are just a pain to shoot down.