quiet_samurai said:
Just the generic starting pistol you recieve on the first level of everygame that involves shooting others.
Eh, not really. Some games keep the little peashooter useful. In Half Life, for example, it's useful to kill headcrabs, and if you can spare the time it's better to use that than the submachinegun, because they use the same ammo but the gun damages 8 per shot, while the mp5 damages 5.
Doom 3's gun is also good for killing spiders.
And Unreal's dispersion pistol becomes the most powerful weapon in the game once it's upgraded (too bad they removed the one-amplified-shot-kills-everything feature in the patches).
"You must destroy this radar installation single handedly, you are outnumbered, and at some point every enemy in the complex will know you are there...... take this pistol and one spare clip.
This is not entirely a silly premise; in WW2, the US manufactured very cheap, single-shot handguns that were meant to be dropped behind enemy lines to be used by resistance forces. You were supposed to kill an enemy soldier with the tiny gun and get his weapon.
Of course, such a premise in a videogame makes sense only if the organization sending you to shoot up the enemy force is a penniless band of rebels, or something. If it's a big MegaCorp you'd think it wouldn't kill them to give you a damn assault rifle at the very least.
As for actual examples, I think Fallout 3's silly .32 revolver definitely counts. However, in the end I had huge amounts of 10mm ammo that I never used (I liked the Zhuanwhatever assault rifle a lot more than Sidney's SMG), so I modded the revolver to use 10mm, upped its damage a bit, and used it during easy random encounters. It made me feel all western-y to shoot five shots, reload, and shoot a few more all in a single VATS turn.

all it lacked was Rushuna Tendo's cleavage reload capability...
The chinese pistol sucked too, but the unique version set people on fire, which can be considered as a significant plus.
Morrowind's various tiny daggers and small swords. After the first five minutes of gaming you're already trying to get the biggest sword you can get your hands on, and everything smaller is basically only useful as loot.
Shadow Warrior's Shurikens. Seriously, did anyone EVER use them? You threw three in a wide pattern, only the one going straight forward ever hit anything, and did less damage than a uzi shot.
I've come across many more sucky weapons, but I can't remember them right now.
Edit: whoa, long post. I really do have too much free time.