Pick a name from Warhammer 40,000 and you'll find a few billion people (and xenos), at least, shitting their pants in fear from the mere thought of them. Maybe a few 'Crons, a few 'Nids, some Dark Eldar or someone straight from the Eye of Terror, and even heroes of great renown find that they have to draw on every ounce of their resolve not to run away or die. Exceptional cases follow:
The Night Lords. Entire solar systems have surrendered rather than battle them (tip: this doesn't stop them from butchering you).
Kharn the Betrayer gets points for being so Axe-Crazy that even other Khorne Berzerkers have been known to freak out when he shows up. It's not just that he's an unstoppable murder machine, he's an erratic, teamkilling unstoppable murder machine.
Within the Imperium, Inquisitors. Not just because they're personally badass, although many are, but because they have effectively unlimited power. They can order your whole planet sterilised or just take you away to torture, and no one will, officially, say anything. Just the sight of their badge of office has been known to make people soil themselves.
The Necron Pariahs have this as their entire hat. In a certain area around them, almost any creature experiences crushing, debilitating dread. In a Ciaphas Cain novel, the crack Stormtrooper squad that had been shown to be far and away more cohesive, ruthless, and cunning than any Guard squad Cain had ever fought with falls completely apart in the presence of Necron Pariahs, gibbering and crying and so forth while the Pariahs casually slice them apart.
The Necrons in general. One of the Word Bearers novels has a Word Bearers Dark Apostle, whose job is to give bloody praise to the gods of the warp and thus must have a decently strong will, respond to the appearance of a large Necron ship in-system by running up the Buckets of Nope flag and sounding the retreat.
Tyranids seem to be scared shitless of the Necrons, if you see how much effort they put on evading the Tomb Worlds, even if this can be debatedly simple pragmatism, because Necrons are metal and hence are not edible. (Although the Tyranids do give the Outsider at least one hundred light years of breathing room at all times, even though the thing is imprisoned in a Dyson Sphere. note The Outsider, for the uninitiated, is one of the Necron C'tan Gods, an Eldritch Abomination that eats stars - this one was tricked into eating one of its own and promptly went batshit insane.)
Even the simple thought of a Tyranid Hive Fleet approaching a planet is capable of inducing mass panic in everyone, jamming all the outgoing transports as full as they'll go and then some, but even if the whole race is absolutely terrifying, some notable beasts have to be mentioned:
Lictors are already camouflaged assassin-beasts that can come out of nowhere and eat your face, but the unique Lictor known as Deathleaper combines it with an intelligence that let it deliberately raise itself to Dreaded levels. On the shrine-world of St. Caspalen, rather than assassinate the reigning cardinal and create a martyr, it infiltrated his bunker and slaughtered everyone inside except him. Then it did it again the next day. And the next. After the tenth, he was a paranoid wreck who was massively hampering, not bolstering, the planet's morale.
The Doom of Malan'Tai, the first Neurothrope, is this for the Eldar. Largely ignored by the defenders of Malan'Tai craftworld, who were concentrating on killing the huge monsters and scuttling swarms instead of the unassuming little floating thing, it managed to reach the craftworld's Infinity Circuit, where the souls of dead Eldar are kept safe. Or were, until the Doom of Malan'Tai earned its name by psychically sucking a few billion souls out of the circuit and using the resulting juice to slaughter every living defender by itself.
The Eldar Harlequins. To put it into perspective, the Dark Eldar (who probably counts as an example of this trope as well) are a race of murderously psychotic beings with an almost genetic urge to rape and torture people in horrific ways, are utterly terrified of the Harlequins and dare not refuse a Harlequin troupe access to their normally impregnable inter-dimensional city, even if they're just there to put on one of their shows. Monster Clown, taken Up to 11.
It's implied that the Harlequins themselves are scared shitless by the Dark Reaper Phoenix Lord, Maugan-Ra. He certainly earned it, though, seeing as he once took on an entire Tyranid Hive Fleet all by himself, and survived note To wit, entire Space Marine Chapters have been wiped out trying to do this.
The Solitaires stand out even among the Harlequins, being the only ones who can play the role of Slaanesh in dramatizations of the Fall of the Eldar without losing their sanity. Eldar are so afraid of Slaanesh that they won't even say its name — of course they'd be scared of an Eldar who pretends to be Slaanesh.
Commissar Yarrick is notable for being this to Orks, who literally have no fear of death. The thing is, Orks believe that he's an unkillable badass, and that belief indeed causes him to be unkillable and badass (the fact that he lost an arm to an ork and promptly killed and looted the same ork's artificial arm to use it himself, and got himself a laser-shooting eye after hearing the orks heard he could kill with a glance, only adds to this). He has a special rule that means if he's killed by a unit in an Ork army, he stands a very good chance of getting back up.
The C'tan Nightbringer is the in-universe Ur-Example of this trope. He is the grim reaper incarnate, so terrifying that he imprinted the fear of death on every race in existence except the Orks and the Nids.
The famed Imperial Guard Ork Hunters who operate in the the jungles of Armageddon don't scare easily - after all, their homeworld of Catachan is one of the most deadly places in the entire galaxy, home to man-eating zombie plants and scorpions the size of tanks. However, even they tell stories of Snikrot, an Ork Kommando whose knowledge of stealth and guerilla warfare rivals even that of any Eldar Pathfinder. They tell stories of Snikrot's scalped and eyeless victims left to bleed to death, or how he can pass through a throttlevine grove without disturbing a single leaf, or how he loots the dog tags of the Guardsmen he kills so he can whisper their names to the jungle moon.
The Eldar fear Slaanesh so much that their various cultures are primarily based on ways to avoid losing their souls to it. The Craftworld Eldar live repressed monastic over-specialized lives and stick their souls into rocks, the Exodites are Space Amish who live on the fringes of the galaxy far away from the Eye of Terror and work very hard to keep themselves hidden from Chaos, and the Dark Eldar (those Eldar who haven't abandoned the hedonism that birthed Slaanesh in the first place) offer up the souls and suffering of others in place of their own so they can keep indulging themselves (and in a society entirely based on Klingon Promotion, there's no backstabbing during a realspace raid, because that's when they're most vulnerable to having their souls taken). Eldar refer to Slaanesh only as "the Great Enemy", and if Dawn of War is any suggestion, saying Slaanesh's real name is profanity.
Warhammer, the fantasy-themed sister universe of the above, isn't lacking for these either.
Archaon, the overlord of Chaos. The warriors of Chaos stand out as being an entire race of The Dreaded, and this guy is the biggest, baddest of the lot. He has all four of the Chaos Gods backing him, and he has almost destroyed the world several times. (As of The End Times, he has destroyed the Old World. Chaos won, Archaon accomplished his mission, game over, buy our new Warhammer: Age of Sigmar stuff.)
Malagor, most powerful and infamous of the Beastman shamans. Also known as the Dark Omen, the Crowfather, the Despoiler of the Sacred, and the Harbinger of Disaster. So feared is he that the Cult of Sigmar in the human Empire vilifies him as the epitome of sin and blasphemy. He actually has an in-game special rule that prevents enemy units from using their general's Leadership; in other words, Malagor scares the other races so much that their own leaders can't keep their troops in line unless they're right next to their troops.
Also from the Beastmen, Gorthor. Even the Beastmen themselves were shit-their-pants terrified of him, as evidenced by his name being Bray-tongue for "Cruel". You want to know why? Many Beastmen wear human skins as a matter of course. Gorthor wore the skins of Beastman shamans. Most Beastman wouldn't dare to touch a shaman, and this guy went around wearing their skins. As for how the humans view him, well, they talk about him the way they talk about Archaon. Archaon is still alive. Gorthor died one thousand years ago.
The Ogre Bragg the Gutsman is this to his own people, because he's the only Ogre who dares to disembowel his rivals, which is, to Ogres, the most cruel, disgusting and horrific way to die imaginable. And not only that, he's brutally good at it, carrying a weapon tailor-made for hooking right above an Ogre's belly-worn protection and scooping everything out. Thankfully, many argue that Bragg couldn't become Tyrant in his own right because so many Ogres are too scared to stick around and obey him. Bragg himself has been kicked out of many places this way, and is more than happy to wander the Earth plying his gruesome trade somewhere else, so he's perfectly happy with this reputation as long as he keeps getting enemies to disembowel.
With a name like Nagash the Undying, you're bound to be this. He's the inventor of Necromancy. The living humans of Nehekhara refuse blankly to speak his name, and the only time in history the Skaven united was to bring him down, and when they failed, the entire empire stayed away from him.
Mentioning Shadowblade in a room full of Elves is a good way to make them all go deathly quiet. He's the greatest assassin the Dark Elves have ever produced, and more importantly, their best infiltrator. (Most assassins can hide in their army's forces; Shadowblade can hide in the enemy army.) He's called Shadowblade because he personally killed everyone who ever knew his real name, save maybe for Malekith, and that's only because he's on Malekith's payroll.
In counterpoint to Shadowblade is Alith Anar, the Shadow King of Nagarythe. After Malekith declared war on the High Elves and fled Ulthuan, but not before killing all of Alith Anar's family for staying loyal to Ulthuan, Anar was named as his successor to rule over the kingdom of Nagarythe. Since that day he has waged unending war against the Dark Elves, with a personal kill count of thousands within the first few years, and not one of those he kills dies easily. Just like Shadowblade is to the rest of the Elven Kingdoms, the Dark Elves refuse to even speak his name. Malekith himself is scared of him, and he has a prophecy that tells him specifically how he's going to die, and yet he is still scared of the Shadow King of Nagarythe.
The Beastmen speak in hushed whispers of an entity known as "Shaabhekh", which is Bray-Tongue for "Soul-Killer". Indeed, killing is what he does well, having slain untold thousands of Beastmen over the centuries. They speak of the fabled Green Knight of Bretonnia.
Harald Hammerstorm is an Ascended Extra (a warrior smashing a skeleton from an early edition's artwork) who's just about the only one capable of causing fear in the undead. Ironically, he's killed by vampires.
In Warhammer: Age of Sigmar, the Lizardmen have become this to the Skaven on a species-wide level, due to a form of ancestral memory.
Anyone in who isn't at least level 20 will metaphorically and/or physically crap their pants at the news that the Tarrasque has awoken. The beast, while occasionally fading into myth due to its long hibernation periods, is NEVER totally forgotten, as when it IS awake, it embarks on rampages of destruction and feeding that can destroy whole countries. Even if it is defeated, it requires the use of the top-level arcane spell wish to keep it from just brushing off death. Only the most powerful spell in the game can kill it.
Out-of-character, rust monsters have a fearsome reputation for their ability to eat your weapons and armor, leaving you defenseless — the iconic "screw you" monster. (Ironically, they can't eat flesh, and for races that don't use metal, can be as friendly and tamable as puppies.) Similarly, the adamantine clockwork horror can throw disjunction and disintegrate at will, destroying magical gear and living creatures that fail their saves; for some reason, this monster was given a Challenge Rating of 9, far below what those abilities alone deserve.
On a meta level: dragons and even the Tarrasque may be met with a cry of Charge! from all around the table, but throw a vampire at the party, and it's "Run away! Those things can Level Drain!"
Vecna, the Lich God of Secrets, is so feared that few mortals even say his name aloud; instead, most use his titles, such as The Maimed Lord, the Whispered One, and the One Spoken Only in Whispers. Even when he was mortal, he was so notorious for his cruelty that few dared say his name, most referring to him by his original title, the Master of the Spider Throne.
3rd Edition's sister product d20 Modern has the Frightful Presence Feat, which requires hostiles of lesser level to make Will saves or become afraid just by seeing the character who has it. The same ability appears in the 3rd Edition Dungeons & Dragons but is drastically more difficult to obtain.
In Forgotten Realms, the Simbul used to throw Red Wizards into mindless panic just by appearing. Mostly because of her bad habit of killing them on sight. That, and being probably the most powerful magic user on Toril. (Some descriptions state that as far as level and abilities go, she's even stronger than Elminster.) Others don't run for cover, but still instantly sober up at a mention that she may or may not be involved — like Cormyreans did in All Shadows Fled.
The Lady of Pain is this to anyone who lives in Sigil in the Planescape campaign, and with good reason. It takes a lot to make her angry at you, but if you do make her mad, she locks you up in an eternal maze. (And that's if she's in a good mood. If someone catches her on a bad day when they make her mad, then magic is going to be needed to identify the body). Also, if you see her physical manifestation, you should probably just go the other way. She seldom speaks, and just being unfortunate enough to be touched by her shadow you will die a horrific, and brutal death-of-a-thousand-cuts.
The Kindred fear the Antediluvians. They were the grandchildren of Caine, who cursed them and their children with their clan weaknesses. The Camarilla as a policy denies their existence, while the Sabbat want to destroy them and take their power. One of them awakening from torpor brought about the Week of Nightmares and several factions had to team up to fight him. The Technocracy authorized absolutely anything to stop him. Hitting him with a magically-enhanced nuclear weapon only weakened him; the Technocracy readjusted three orbital mirrors to hit him with the equivalent of three Suns to finally dust him.
Each Antediluvian has access to the tenth level of their Disciplines, which allows them to do anything they can imagine with their powers (such powers are typically named "Plot Device"). The Gehenna book illustrates just what the Antediluvians will do if they awaken in the Final Nights.
The evil godRovagug is this among the other gods. He wants nothing less than the destruction of all creation, has slain countless of his fellow deities, and even gods who hated each other joined forces to bring him down and imprison him within Golarion itself. Even down there he's far from harmless, as his struggles cause earthquakes, and every now and then he releases some form of Spawn to wreak havoc across entire nations. One of these is Tarrasque (see above). Yes, the freaking Tarrasqueis this guy's kid. What really sells it though, is that the 4th edition of the Bestiary statted freakin' CTHULHU of all things. Pathfinder includes the Great Old Ones and Elder Gods among their divine residents. And THEY allied with all other gods against Rovagug AND COULDN'T DO ANYTHING TO HIM. And if that wasn't enough, there are dark rumours that Rovagug might have a PARTNER in Groteus, the God of the End Times. It basically boils down to this: Rovagug wants to and WILL destroy all of the physical realms. Groteus will snuff out every soul and metaphysical realm. So that EVERYTHING will be Deader Than Dead.
At low-levels, Stirges are this, especially swarms. Bat-winged, cat-sized mosquitoes. All they have to do is hit and they drain Con.