Honestly, Silent Hill 2 isn’t really scary. SH2 is like a suspense film such as Alfred Hitchcock’s Rear Window or The X-Files, not a horror film like The Exorcist or Poltergeist. It goes for a spooky horror atmosphere, but there is nothing in the game that is scary or makes you jump out of your seat in fear or anything. Similar vibe to the X-Files PS2 survival horror game, if that one was a little goofy. Its not like the first Silent Hill, which had jumpscares and an over-reliance on blood/gore/grotesque in order to create its horror. SH2 does have some of that, but compared to the first game it is toned down very significantly. And this is one of the reasons I say SH3 was such a massive step backwards. Like, from a technical standpoint SH3 is better than SH2, but from a narrative standpoint the game is worse than the first game in my opinion. Still better than 4 and later though.
Silent Hill 2 is SLOW. Which is one of its great strengths in creating tension. Similar to an Alfred Hitchcock film, it doesn’t try to jump scare you, but rather it wants to make you feel anxiety or nervous about what could happen next. Some people confuse this with fear, as they are similar.
I can kinda describe it like the original Nightmare on Elm Street (not the bad 2012 or whatever reboot), since that movie also wasn’t really that scary. It was hard to be scared of Freddy Kruger when he had such goofy one-liners constantly in the film.
Overall SH2 is a high recommendation from me, but it may not fit your exact criteria.
Ironically, every Resident Evil starting from RE2 is an action horror game, not a survival horror like the original game.
Other games that are macabre themed would be games like Castlevania, Ghosts n Goblins, etc. I liked Rusty, which was a Japan only PC9800 game which cloned the gameplay of Castlevania. Anyone that likes Castlevania owes it to themselves to play Rusty.
MedEvil I think is a 3d platformer, but I never played it so I can’t speak to its horror strength.
Luigi’s Mansion is obviously a goofy pick here.
Dead By Daylight (I can’t believe I am recommending this since it has such a toxic community) is a horror themed 4v1 multiplayer game that, once you play a few games, becomes not scary at all except for certain moments when you aren’t paying attention and you get jumpscared by another player walking onto your screen. It features a wide collection of characters from popular horror franchises, as well as characters from Dungeons and Dragons and Lara Croft for some reason. Just watch out for the toxuc community on this one. Most of my matches end before 20 minutes, and finding a match is usually pretty fast but I mainly play Killer and apparently less than 20% of the entire playerbase plays Killer.
There is also Pascal’s Wager, which is a soulslike but I haven’t played a whole lot of it, just 10 minutes. Seems to play similar to Bloodborne.