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.
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.
Special mention to 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 Eldar Harlequins. To put it into perspective, the Dark Eldar (who probably count 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 Eleven.
And 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 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.
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 who's 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. On the tabletop, if Snikrot wins a melee against a squad, they suffer a substantial Leadership penalty whilst determining if they run away.
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.
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 Gutsmen 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. 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.
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.
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.
Anyone in the Dungeons & Dragons universe who isn't at least level 20 will metaphorically and/or physically crap their pants at the news the 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. 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.
Every single dragon, ever. Even the good ones! Fortunately they can control this power.
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.
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.)
The Kindred of Vampire: The Masquerade fear the Antediluvians. They were the grandchildren of Caine, who cursed them and their childer 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.
In Pathfinder, the evil god Rovagug 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 the Tarrasque (see above). Yes, the freaking Tarrasque is this guys kid.