The guy from Doom wore a spiked fist and the 'Hand of God' was from Rise of the Triad (home of other classic weapons like the Dog Transformation and the Flamewall.) Rise of the Triad had unlimited pistol ammunition.The details are a bit sketchy, but I remember a game from the 90's, first person shooter that was based on PC. Yeah, I know that narrows it right down. When you ran out of ammunition you were left to fight with your fists and I remember there being a spiked ring on the right hand. I used to always try and finish the game with only the spike. My favourite weapon on all time.
I remember the feature weapon, like Quake & Doom's BFG, being the 'Hand of God' Another favourite of mine. Just can't remember the name of the game.
More modern gaming weapons I like are the Sonar Goggles from Splinter Cell and Lincoln's Repeater in Fallout 3.
Oh, and Lilarcor the Talking Sword from Baldur's Gate II, and everthing from Ratchet and Clank.