My top list of atmospheric RPGs:
Souls games and Bloodborne. Top notch.
Anvil of Dawn. Very moody, and great art. Dungeon layouts were great but constantly putting rocks on pressure plates was tiresome.
Ultima Underworld I and II. Still top dogs as far as I'm concerned, and I love the way both games complement each other. The Void in UU2 is criminally underrated and it's beyond sad that despite technology being leaps and bounds ahead, no-one has even tried to better it.
Ultima VII parts I and II. Both are actually rather atmospheric and, as far as story based RPGs go, I prefer a simple plot with simple writing, in which the player must collect clues and note them down as they travel from town to town. It certainly beats going from town to town and accepting a checklist of structured, boring, and poorly written quests; and it's infinitely better than the trauma of playing pick and choose between a short list of "serious" "dialogue" options, which have limited effect on the game in the first place, and in which the chief difficulty the player faces is picking the least cringeworthy option. Less is more, and I still remember the angry drunken mage in Serpent Isle, with his few lines, more so than the overwritten, cringeworthy NPCs of modern RPGs.
Gothic I. I'm sure the sequel was a good game, and I spent more time on it, but I just don't remember it as much as original Gothic setting, with the prison camps.
Diablo 1. Apparently a seminal game. If so, I'm still waiting for the results. Everyone aped Diablo II, where you click on a rat and it explodes into 500 pieces of junk. I would like to see a proper sequel to the original Diablo.
King's Field and Shadow Tower. KF4 especially is a masterpiece in terms of level design and atmosphere. If KF4 had had a desktop release then I'm confident that it would have been lauded by Codexians, its dungeon design being of Looking Glass calibre. As such, it remains a console obscurity.