Hm, console RPGs with interesting or noteworthy stories? Generally I don’t care much for most RPG storylines, but here are a few picks that I think surpass the CT/FF6-8/etc. you mentioned:
Breath of Fire: Dragon Quarter (seconding skankin) – PS2 – It sells itself short calling itself “a tiny tale of time concerning war between friends” in many ways. 20 hours for one play through isn’t even much shorter than average. The atmosphere of this game is great, as the whole (or not the whole? I won’t spoil) game takes place in an underground semi-dystopia. Plot is to the point, and sharp. However, it’s the great turn-based battle system incorporating TRPG elements and dungeon exploration that make the game a sleeper hit fan favorite after these several years since its release
Valkyrie Profile – PS1 – In this game, every time you recruit a character (happens a lot), you watch a scene of how they died. Some are quite poignant, for a video game, and many of them are related, so it keeps that interesting. There are a few plot twists if you go down the “A” ending path, and the plot is overall good. The turn-based action battle system is quite nice too (imagine setting up a combo with four different characters attacking the same apponent, resulting in a guard crush, some ground bounces and air juggles, etc.), and dungeon exploration involves some platforming and puzzle elements, spicing up the game. Music includes a lot of awesome metal tracks and is very diverse. On a trivia note, Josh Ballard (aka rashreflection) did a speedrun of this game. It’s up on speeddemosarchive.com.
Vagrant Story – PS1 – According to the creator (I forget how he worded the analogy exactly), this game is something of the independent film to Final Fantasy’s summer blockbuster. It’s got both style and originality, and it packs a unique presentation. The action battle system may get a little tedious or not, but for what it’s worth the game is only one of a handful ever to get 40/40 from Famitsu. The battle system requires some very precise timing to score high attack chains and defend from attacks, so that may be interesting to you.
Radiata Stories – PS2 – This is overall a very lighthearted, funny game that may seem shallow plotwise, but the direction is really spot on, and there really is a relatively great amount of depth under the surface that many people miss. It’s pretty much all you can expect of a good coming-of-age story. In any case, it has very humorous scenes throughout, and the actual gameplay may be really fun or just a little fun depending on your preferences. Battles are a little shallow though.