Don't waste your time with Silent Hill 2 unless you find running around a foggy town teeming with handicapped people who try to vomit on you to be scary. (and yes I played through the entire game and I hated it even after finishing it, so feel free to flame)
Definitely check out RE0 - Code Veronica, Fatal Frame 1 & 2, and the Clock Tower series (you'd probly like 3 the best, seeing how the first 2 were point and click games).
I also highly recommend Siren for PS2 and Eternal Darkness for GC if you're looking for scary games in general.
Some other games that *MIGHT* be of interest are the Onimusha series (basically RE with swords, and great gameplay -- esp 3), Galerians (PS1) and Galerians: ASH (PS2), Disaster Report for PS2 (Survive Natural Disasters! WOOO!), and Dino Crisis 1 & 2 for PS1 (RE with Dinosaurs) but avoid 3 on XBOX.....its not so hot.
Hope that helps and good luck tracking the games down!