A prime example of this trope would be Captain Linebeck, from The Legend of Zelda: Phantom Hourglass. He acts the part of the dashing hero, but it's quite obvious to everyone that he's really just an enormous coward. Still, even he proves useful, if largely ineffectual, when the chips are down so perhaps he can be excused...
Sir Roderick Ponce von Fontlebottom the Magnificent Bastard from Jade Empire fits this trope to a T. He's an obnoxious blowhard who seems to be a cross between Don Quixote and a British Imperialist. Subverted in that you can fight him, and he has the strongest weapon in the game (a blunderbuss! In a game based on ancient China! Not quite fair).
In MediEvil, Sir Daniel Fortesque earned his knighthood during times of peace by being a very entertaining, and convincing, storyteller. When a real enemy appeared, he took the first arrow right in the eye. Not that this stopped him: the king (sensing that the public needed to hear something hopeful after the horrific battle) gave him a hero's funeral for "killing" the Big Bad.
While certainly a braggart, leading the charge proved he had the courage to be a real hero. After returning, Fortesque subsequently Took a Level in Badass and turned out to be a real hero after all when he destroyed the Big Bad several centuries later.
Captain Qwark from Ratchet & Clank. He becomes much more heroic in the later games however.
Anomen from Baldur's Gate II — a mild example, since he is a competent warrior in-game. The game's actual Knight in Shining Armor and The Munchausen (Keldorn and Jan Jansen, respectively) aren't fooled by his tall tales (like killing a Wyvern single-handedly, or having defeated several hill giants during a battle) for a second, however. Neither are just about anyone else, come to that, though most of them are too polite or disinterested to call him out on it.
The Protagonist can also call Anomen on this exploits, and tend to run into a few enemies of non-importance who have an overinflated sense of might.
The Elder Scrolls III: Morrowind includes an NPC named this. Considering he's a great hunter often referenced in conversation, who never leaves the tradehouse. (Admittedly, that might just be because he isn't an important enough NPC for the scripting effort necessary to avoid that, rather than actual proof that he never leaves the tradehouse.)
The Elder Scrolls IV: Oblivion has an inversion. When closing one of the Oblivion gates you need to find a group of arrogant squires, who have created their own knightly order. When you meet them they are arrogant and boastful, but are actually capable of defending themselves. When you close the Oblivion gate and return to Cyrrodil, their attitude changes completely: they admit they were scared and thank you for saving their lives.
Daxter in Jak and Daxter makes little hesitance when he talks proudly about his "many heroic deeds". The only one who believes or is even remotely impressed by this is his love interest, Tess. He does have his heroic moments, being the one who gets Jak out of prison, and defeating The Dragon just before that. Of course, the latter depends entirely on what you believe, as his entire Gaiden Game is either him telling the truth, or more yarn spinning.
The Head Editor in Final Fantasy Tactics A2 is a perfect mold of this trope. He claims to be able to handle his own, but no matter what mission you do with him, he's ALWAYS at level 10 and half of the time, he gets too scared or nervous to fight, relying on your clan to do the dirty work. His only ability is Camouflage, which lets him hide. In one mission, he brags that the Owner (his boss) is more powerful than he is, but he is only at level 1!.
Ziegfried in Final Fantasy VI, who tries to disguise himself as the greatest swordsman alive. The first time he attacks you, he opens with a flurry of eight weak physicals when you have a character who can inherently counter randomly, and an item that lets someone else counter randomly too. Hilarity Ensues.
When you meet him again after the Time Skip, he claims that someone was running around pretending to be him. If you fight him in the arena he turns out be a very powerful opponent, so he's probably telling the truth. Either that or he did some serious Level Grinding over the Time Skip and doesn't want to admit he was such a wuss.
Actually it's true : the badass warrior, champion of the Colosseum is Siegfried, Ziegfried was impersonating him in the phantom train.
The Golem Boss in Chrono Trigger tries to scare you with a big countdown. The problem is he's afraid of heights. And you're fighting him on the wing of a plane. His true nature reveals itself after about ten seconds.
In Psychonauts, Coach Oleander makes claims about his impressive army background to the point that the inside of his head appears as a twisted battlefield and contains memories of himself seemingly single-handedly winning some type of war. Upon a second visit, however, you can find his true memories which show him being kicked out of the army, air force, navy, and even the army kitchens (though this is more due to his height than his cowardice or incompetence).
Subverted; He gets Character Development and becomes a Cowardly Lion near the end of Chapter 5. Afterward he's not quite as much of a lying braggart as he was when you first met him. He himself even states that he's found something "far, far better" than treasure or glory during the ending of the chapter.
Super Paper Mario also featured Flint Cragley, though he trades much of the cowardly flaw for simple incompetence, and is obviously a Fake Ultimate Hero from the first time you meet him.
Luigi, while normally a Cowardly Lion in the main series, is portrayed as a Miles Gloriosus in the Paper Mario series. In the second game, he tells Mario (who gets bored and falls asleep) about all of his exploits in the Waffle Kingdom, but his various partners tell the real truth about his adventures, always messing up. Also, in Super Paper Mario he seems to act brave, but he is ultimately found scared in the Underwhere, only cheering up when Mario finds him.
If you notice... He only brags about what he does when he talks to Mario, which kind of makes it look more like he's trying to make his big brother proud. Unlike some other examples, his stories do hold truth to them, he did do all the things he said he did... just not quite as heroically.
In Ōkami, the 'great swordsman' Susano is actually an incompetent coward. The main character, a magical wolf-goddess named Amaterasu, sometimes hangs around and helps him fight, although he never seems to notice that his techniques only work when the 'annoying wolf' is around.
Interestingly, by the time you fight Orochi, this trope has been largely deconstructed. Susano doesn't want the reputation he's been bragging about, to the point that he started the whole incident by trying to prove the legend of his ancestor false to free himself from the burden of living up to it. Issun mocks him for quivering in fear in his sleep, unaware that his dreams contain things worth being afraid of. And he catches on to the fact that his heroic feats are not truly his own, embittering him to the idea that the gods expect him to save the world... and even then, Susano's Amaterasu-assisted slices are more effective than when Ammy does them on her own.
In Mass Effect 2, a crazy fan of Commander Shepard (the player character) can be found essentially cosplaying as the Commander and attempting to go around the galaxy righting wrongs and helping people. Although he claims to be a badass he can be scared off by shooting him in the foot or kneeing him, and overall he's incredibly incompetent. There is one ending to his side quest that shows he has some guts, but the end result is that he is killed attempting to stop a petty crime.
If you choose the paragon solution of pretending that he actually helped to solve a real crime he walks away transformed and you later hear on the Illium PA system that he is now leading a charitable organisation that helps out the orphan victims of slave raiders.
In Mass Effect 3, he is still somewhat incompetent (inadvertently supporting the bad guys), but when you point it out he tries to take a bullet for Shepard. If you completed a certain sidequest in Mass Effect 1, it turns out that someone else had replaced the shooter's ammunition with blanks.
And then there's Niftu Cal, the self-proclaimed "Biotic God". He's actually high off whatever passes for tits on a volus. You can either convince him he's for real (which gets him killed) or convince him to go sleep it off.
Maniac in the Wing Commander series, at least outside of the cockpit (in the cockpit, he's the 11th highest ranking ace in a 3 decade galactic war). The commanding officer even tells you to shoot him down if he gets in your way - and to use your guns, because it'd be a waste of missiles.
See his and Blair's original encounter with "Seether" in the Wing Commander IV intro.
Later in that game, when forced to back up his words in an argument with "Gash" Dekker if the player decides to not intervene, the viewer gets a practical demonstration of Maniac not living up to his boasting.
Flint/Magpie from S.T.A.L.K.E.R.: Call of Pripyat. The first time you get to Yanov station, you'll probably hear him bragging about his exploits, like finding a new route between Zaton and Yanov, killing a Controller, finding the Oasis, clearing out a bloodsucker lair and killing a Chimera. The catch? All of these are things that you did. Fortunately, you get multiple opportunities to call him on his bullshit.
"The Fearsome" Captain Blade, a Posthumous Character in the Borderlands 2 "Captain Scarlett and her Pirate's Booty" campaign. Though no coward, he was a pacifist who manufactured the image of a bloodthirsty, fearless cutthroat in order to keep his pirate crew in line. When his crew discovered that he had faked his executions of insubordinates and had never killed anyone in his life, they banded together to violently depose him and he ended up killing them all in self-defense.
Piston, the main enemy of Mr. Torgue's Campaign of Carnage, is a very large, very scary man who holds the #1 rank in Torgue's badass tournament. However, he's a blowhard and a Dirty Coward, afraid of any kind of fair man-to-man confrontation. When you finally face him one on one, he's relatively easy to beat.
Master Hamon "the Holyfist" from Final Fantasy XIV (A Realm Reborn) is shown to be this during the level 15 Pugilist class quest, but he gets better later on.
At the outset of the Happy Trails expedition in Fallout: New Vegas: Honest Hearts, one of the expedition members is "Deadeye" Ricky, a badass who fought deathjaws (which are like deathclaws, but have bigger teeth), sniped a Brotherhood of Steel paladin with his 11mm submachine gun, is an honorary member of Vault 22, and has a third nut that glows in the dark. All of that is a lie. The only things he has are a broken PipBoy, a stolen Vault 22 suit, a 10mm SMG, and a crippling addiction to Psycho. If the Courier can see through his lies, he can get Ricky booted out of the caravan or blackmail him into acting as a pack mule.