Going to be keeping it short just to keep a small database of weapons that are either solid across all modes or particularly good in a specific situation, weapons that are just okay in either PvP or PvE alone or are sub-par as a whole across all modes aren't included neither are weapons that are just plain outclassed by other weapons in the same category. Order is listed is based on usage rate in PvP at the time of the INITIAL CREATION of the posts.
1) Better Devils - Balanced stats between damage and fire rate with Explosive Payload which serves to further increase it's damage and help extend it's range as Explosive Payload doesn't have damage fall off.
2) True Prophecy - Future War Cult Faction weapon similar to Better Devils, but lower fire rate and higher damage. The increased damage does not outweigh the loss of fire rate or reload speed compared to Better Devils and it doesn't handle as well either. Timed Payload is a delayed version of Explosive Payload which is both weaker and prone to damage fall off. True Prophecy is good in PvP, but lacks in PvE.
3) Strum - Kinetic Exotic Hand Cannon. Nothing special on it's own especially for an exotic, but when combined with Drang to help reload it, it gets a decent buff. It's above average stats is why people use it, but it doesn't have a lot of bells and whistles for being an exotic.
4) The Old Fashioned - Drop Mag + Kill Clip means it has great DPS, but potentially wastes ammo through Drop Mag and Kill Clip requires activation then a reload which cuts down on actual utility. With kill clip active it gains a 33% damage boost and it's a solid balanced weapon, but better for PvE than PvP.
5) Dire Promise - Dead Orbit Weapon similar to The Old Fashioned. Lightweight is a big perk for PvP and it's a solid weapon with low recoil and decent range for a Hand Cannon making it fair for PvP while Triple Tap extends it's usefulness for PvE especially as a boss killer where you can repeatedly crit through out the mag and take full advantage of refilling the mag.
6) Midnight Coup - Lightweight for PvP and Outlaw for PvE. Outlaw lets you reload with impunity after a precision kill and reloading tends to be common disadvantage of Hand Cannons but it's hard(er) to make use of in PvP against what should be smarter enemies. Lightweight of course benefits it's PvP.
Better Devils is the best PvP Kinetic Hand Cannon and possibly the best PvP Hand Cannon period edging out even the Exotic Sunshot, it's overrated for PvE IMO. True Prophecy is vastly overrated for both PvP and PvE largely because it's an event weapon and people want it to be better than it is. Sturm isn't worth your Exotic slot unless you don't have anything else. The Old Fashioned is better than True Prophecy IMO for both PvP and PvE if you don't mind some wasted ammo in both and if you use your Energy Weapon effectively you should be able to waste that ammo. Dire Promise IMO is underrated and this largely comes from being a faction weapon and competing with True Prophecy for loyalty, FWC fans will say True Prophecy is better while Dead Orbit members itself likely will use Better Devils as Dire Promise doesn't get the hype FWC weapons are getting, but IMO it's better than True Prophecy overall. While for PvP it is more forgiving than True Prophecy a good player with True Prophecy will win out in a PvP duel however in PvE True Prophecy is rather lackluster and Dire Promise shines. Handcannons kill mobs quickly and tend to lack when kill bosses due to lower DPS, but Dire Promise has one of the best DPS among Hand Cannon class with a solid range and Triple Tap is hand built for killing bosses. Midnight Coup is second to none in terms of Kinetic Hand Cannons for killing large groups of weak mobs, it's lackluster for PvP and against harder PvE targets deserving an honorable mention as most of the game are mobs that can be killed relatively easily.
1) Sunshot - Exotic Handcannon that excels in PvE, but has an explosion effect similar to Better Devils with above average stats and great DPS for PvP making it a contender for the best PvP Hand Cannon as well despite being geared for PvE. The Highlighting effect nullifies stealth but doesn't really mark targets as the skill suggest and the burn effect provides bonus damage to an already substantially strong weapon meanwhile Sun Blast is a very strong on kill AoE explosion which can actually get some cluster kills especially when combined with team fire to set off chain reaction.
2) Annual Skate - Energy Hand Cannon with Outlaw, it's strong for PvE and okay for PvP. Since it's an Energy Weapon it frees up the Kinetic Slot for some of the stronger Kinetic Weapons out there like Nameless Midnight.
The best weapon in the game for PvP is considered the Mida Multi-tool which is an Exotic Kinetic Weapon so that means if you want to use it you can't use Sunshot, but the arguably best legendary in the game for PvP is also a Kinetic Weapon and there isn't that big of a difference between Nameless Midnight and the Mida Multi-tool so if you really enjoy Sunshot it's worth your Exotic Slot and you can find something other than the Mida Multi-Tool to fill your Kinetic Slot. Annual Skate is honestly the only stand out Legendary Hand Cannon so if you don't want to have a Hand Cannon main weapon due to range issues Annual Skate is simply your best bet.
1) MIDA Multi-Tool - Kinetic Exotic Scout Rifle. Radar staying active is huge in the Crucible and the Multi-Tool has a monopoly on it, further icing on the cake is the increased movement speed and the fact that Scout Rifles are pretty much the go to weapon class for any sort of vs play. Extremely heavy Aim Assist also helps people use this weapon with very little effort or skill involved which undoubtedly helps contribute to it's status as the top weapon in the Crucible. The MIDA Mini-Tool adding speed when equipped with it further makes the Multi-Tool top tier and the Mini-Tool is solid in it's own right in the Crucible.
2) Nameless Midnight - It's a Scout Rifle with Explosive Payload that alone pretty much makes it the best legendary in the game for Crucible, but if that weren't enough it's got decent stats and heavy Aim Assist to help players use it easily and all that adds up to the reason it's 2nd in the Crucible behind the Multi-Tool. Unlike the Multi-Tool, Nameless Midnight is very good in PvE since you really don't need additional mobility to be successful or the radar Nameless Midnight is simply a better weapon and that shows when Crucible specific perks don't particularly matter.
3) Haunted Earth - Dead Orbit Faction Victory 1, being a conditional release and with FWC having a strong backing we may never get this weapon, but between High Impact Frame and High Caliber Rounds it has all the makings to be a top tier weapon for both Crucible and PvE. A Similar weapon Song of Justice for the New Monarchy is ranked at 30 and is lacking the very strong High Caliber Rounds for the Crucible and Song of Justice is a top tier PvE long range weapon and Haunted Earth has much more useful perks and bonus capacity all while having faster crouching reloads outside of High Caliber Rounds which is what should propel Haunted Earth several slots ahead of New Monarchy's Scout Rifle.
4) Call to Serve - Solid Recoil control with Triple Tap, pure stats make it popular in Crucible and Triple Tap for PvE.
5) Good Counsel IX - Unreleased New Monarchy Scout Rifle. Basically the same thing as Song of Justice but a more useful perk of improved hip-fire rather than reloading when stowed like Song of Justice.
6) Song of Justice - High Impact Frame makes it an actual long range weapon which gives it a decent niche for Crucible and makes it quite good for PvE, you're basically entirely relying on the frame as the surround perks are fairly sub-par.
MIDA Multi-Tool can't be beat for Crucible radar is too valuable and High Caliber Rounds on top of that make it an above average weapon to boot, but again it's sub-par when those Crucible geared perks aren't very useful so I wouldn't use it for PvE personally. Nameless Midnight is touted as the best legendary in the game and it's strong for any mode at any range and it's easy to use, but I personally feel there are better weapons out there especially for PvE like Haunted Earth. Explosive Payload gives it extended range which helps it go a bit past the actual Scout Rifle's decent range but in terms of Scout Rifles it's range is actually not that good for the bullet portion, it also gives it the impact you want to see for the Crucible to flinch opponents. Haunted Earth to me has all the makings of being the best legendary in the game, it has a low Aim Assist so you can control it more easily and it puts the skill back in your hands which will hurt people's opinions of it when they're used to easy mode weapons, but having High Impact Frame and High Caliber Rounds it has Damage and flinch potential to make it a top tier Crucible weapon and since crouching helps avoid Radar detection a little bit reloading while crouched isn't too shabby for Crucible either. Unlike the Multi-Tool all those perks are useful for PvE and the increased reload speed while crouched comes with increased ammo reserves so your bullet supply goes a longer way which can be nice for PvE. The other 3 are good but nothing special worth noting compared to the top 3 it's more of the same Scout Rifles are simply a good weapon class so there are a lot of stand out weapons that are worth looking for.
1) Skyburner's Oath - Energy Exotic Scout Rifle. Slug Rifle boosts damage when hard aiming coupled with full auto maximizing your fire rate makes it a good DPS. For the Empire has additional boosts for PvE most notably ignoring the Cabal Shields and the Leviathan Raid is Cabal so it has boosts there. It's good, but it's also exotic so there are weapons that certainly deserve your Exotic Slot more.
2) Pleiades Corrector - FWC faction rally weapon. It comes with Lightweight Frame and High Caliber Rounds so it's good for PvP, but overrated IMO. Rated #33 for a faction rally weapon means those with it are using it quite a bit, but I'd take the Manannan SR4 over it any day which is rated a much lower #52.
3) Manannan SR4 - Lightweight Frame and Explosive payload, it doesn't have quite the aim assist Nameless Midnight does which likely hurts it's rating. Compared to Nameless Midnight this weapon is better, but the less predictable recoil and lower aim assist make it harder to use and Energy Weapon tend to be just plain better and Explosive Payload kind of works against the purpose of bringing down shields a bit so it's not as popular as it should be given Nameless Midnight's over-inflated popularity.
Manannan SR4 is the top Energy Scout Rifle since Skyburner's Oath really doesn't merit the Exotic Slot if you have something better which isn't that hard to find. Pleiades Corrector is good for PvP all around lack luster for PvE and for PvP it's not one of my top choices personally either classic case of overcompensation. Skyburner's Oath is good for PvE when you know you're fighting Cabal otherwise it's not optimal anywhere for your Exotic Slot and the Leviathan Raid is more custom tailored for the Rat King and it's one place the Skyburner's Oath is better at.