Haunted House for the 2600 will scare your pants off.
Oh, Hexen too.
I came back in here to say Haunted House and Ghost Manor. I'm sure there are a few others on Atari, but a lot of great games have been mentioned already.
I just thought of a funny one that used to scare me when I was little. Tunnel Runner for 2600. When the music changed from calming to creepy and that big face came after you.
Edited: to mention another game that used to scare me with a music change when I played late at night, but definitely not scary Fairy Tale Adventure for Genesis. It was very calming exploring the countryside and as soon as enemies were coming they played a loud guitar rift type of sound.
And a game for this gen that I thought was really scary and two more randoms
Condemned Criminal Origins (XBOX360)
Dracula Unleashed (Sega CD)
Dracula Undead (Lynx)