Hmmm.... well, the most fun guns ever with unlimited ammunition were in the Mass Effect once you ploughed enough heat sinks and stabilisers into them.
The pistol and shotgun from Halo: Combat Evolved
The Spartan Laser in Halo 3, along with the Scorpion Main Battle Tank in all the Halo games.
The triple Bat-Claw from Arkham Asylum (ok, not technically a weapon, but you can grab goons by the throat and pull their thick skulls into thicker steel hand rails)
The double Hidden Blades (give guards the Venetian skull piercing with style), poison Blade and the more high end swords and the broader daggers in Assassin's Creed 2
Mr.47's Silver Ballers from the Hitman games (especially Blood Money, where upgrading is must)
Sniper rifle (Viper variant) from Mass Effect 2
The ridiculously nice looking Greatswords from Fable 1, along with the very nice special axes you could find.
The Assault rifles in Fallout 3
The M4, Javelin missile launcher, MP5 SD, USP .45, and the huge Barrett sniper rifle from CoD: Modern Warfare.
The same again with MW2 just add the ACR with the heart beat sensor.
