The Deus Ex series is good at this. Instead on having a big vast game world they have a condensed area filled with tons of details used for storytelling. You can go into a lot of places like apartments and it says a lot about who lives there. How well kept everything is, what posters they have, the station the radio was on, the stuff in their nightstand etc.

SWAT 4 also does a great job with the environments. One memorable level is where you are trying to arrest a suspected serial killer. When you approach the house everything seems...underwhelmingly okay and the mom pleads with you to not take her son because there's clearly a mistake. As you clear more rooms things get messier with some traps and blocked doors among the clutter making it a claustrophobic and dangerous maze. Eventually you find rooms dug out from the basement with a girl who had been missing for a long time as well as makeshift torture rooms and plans to capture and kill more. It's just a slow descent into their madness and it's quite the experience to play.