Obscure entry time!
Other than the titles that everyone keeps mentioning, I think that one of the scariest games I've come across is Dangan Ronpa, It's in Japanese, but despite a few gameplay gimmicks, is a visual novel at its core, so there are translations readily available online that don't completely spoil the experience. (Most notably the Let's Play on Something Awful and several upcoming projects at this point.)
The plot revolves around fifteen high-school students entering a college for the very talented. All of them, barring the player character, who won a lottery to get in, have nearly superhuman ability at something. (This goes well beyond the range of academics and athletics; among them are a super-gambler and a super-gangster.) The students are all knocked unconscious after they enter the school and awaken to find all of the windows sealed with metal plates and all of the doors blocked shut. A robotic teddy bear with a cartoonish voice appears, claiming to be the headmaster, and explains the school rules.
To escape the school, a student has to kill another person. If there is a murder, then a trial is to be held immediately to vote on who had committed it. If the murderer is correctly identified, then they are executed, and things return to their state before the murder; but if an innocent person is found guilty, everyone except the murderer is executed, and the murderer is allowed to leave.
By the very premise of the game, a lot of named major characters have horrifying deaths. Unusually, the scariest parts are concentrated at the beginning of the game, and the second half of the plot is somewhat less gruesome.
http://tvtropes.org/pmwiki/pmwiki.php/V ... anganRonpa
I'm not a huge fan of the horror genre, so I haven't played the big names people are mentioning like Silent Hill. Those are probably worse.