Someone will have heard of every game, but there are some lesser known ones. A few years back I began trying to find original copies of every adventure game for DOS. More recently I began tracking down Win 3.1 titles.
Many of the higher production value games have already been listed here, however there are a few that were missed.
One of my absolute favorites is The Neverhood.
100% claymation, a kooky soundtrack by Terry S. Taylor, and a hilarious storyline. A sequel of sorts was released as an action platformer for the PS1 titled Skull Monkeys. Robot Bill is epic and one of the most memorable characters of the era for me.
Hell: A Cyberpunk Adventure
The graphics are great for the time, although the controls can definitely be awkward. I actually played through this on the 3DO, so maybe the PC is smoother. Dennis Hopper, Grace Jones, and Stephanie Seymour provide their voices, and in some cases likenesses to the game. Ultimately though, what really made the game memorable for me was the storyline itself. Hell is a very real place in this world, and the government has a contract with Hell to send people it doesn't like there. Your one of the agents that sends people to Hell until one night your fellow agents come to take you away.
DarkSeed (sequel is skippable)
Best played for it's graphic style which was heavily inspired by the father of 'Alien', HR Giger. The game/puzzle design can be confusing with a few overly convoluted segments. It is not a great game, but it is a very stylistic game that is worth a play through,m even if just using a walkthrough to experience the story.
Released fort PS1 and Saturn.
This game falls squarely in the realm of the Discworld series (also not mentioned above, but maybe because everyone has played them?). As far as console "only" adventures go, this is one of the better. It tends to walk the line of humor from Discworld with a style from Toonstruck (also a must play that is not listed anywhere above, but again, probably due to being well known).
This 'myst clone' separates itself from most of the pack in several ways. First of all, there are multiple ways to experience the game. The intro alone has 2 or 3 different sequence of events that can occur. The world itself has a DaVincian style and there is a clear cut antagonist who will attempt to sabotage your progress.
Other games that are definitely worth playing:
The Last Express
Discworld 1 & 2
Woodruff & The Schnibble of Azimuth (edit: looks like that just got posted!)
Unfortunately I could probably list another 30 games, but I'll keep it as concise and focused around my favorites as possible.