The game you're looking for is Grand Theft Auto IV's iCEnhancer modification. Basically someone modded a game from 2008 to make it look like this 1310585147_GTAIV2011071120014857.jpg Here's a huge gallery of it. It's produced a lot of breathtaking screenshots but from what I've heard, very few people can actually get it to run.

The top PC games for this kind of thing today are Crysis 1/2, Battlefield 3, Metro, Rage, and The Witcher 2. Out of all of them, I know that BF3 and Crysis 2 were made to be really easy for PC users to run. The Witcher 2 wasn't, and it's up for debate if it's better looking than either of those. Crysis 1 is also harder, and I've heard some say it's a better looking game than Crysis 2.

But like GTA IV, all these games have mods you can find to make the graphics even better than the developers imagined. I know Crysis 1 has an insane super reality mod.