Most of the police officers in Detective Conan are this, but specially Megure. They're portrayed as competent at their job (a few stories start near the end of a successful investigation on their part, for example) - but Shin'ichi is just that much better than them, and Megure especially has the good sense to defer to him.
Kogoro Mouri can be this when he's not a Clueless Detective—usually this is when he's around but Megure isn't.
Inspector Nakata from the Witchblade anime series. Can be excused due to the abnormality of the cases he faces, but all he can boast in-series is that he's a generally decent guy, not too shy to challenge big corporations, and can put up a bold front when things look ugly. However, he reaches the end of the trail only when put on it with a red herring and ultimately allows himself to be used as a blunt tool for office backstabbing. Not really a bad hound, but has neither the scent nor tenacity of Yusuke Tozawa, whom he calls "hyena".
Most of the cast of Death Note are this to L and friends, especially Aizawa in the final arc, who figures out that Light is most likely Kira, but can't find any good evidence against him.
It's not like they are incompetent, it's just Light works in the Special Investigation Team, too. He manipulates them and he knows how to create perfect Red Herrings and other traps. For him, the police are actually still a threat.
Inspector Kenmochi in The Kindaichi Case Files is a rare example of a Lestrade who is actually genre savvy. After resisting Kindaichi in the series' first mystery, he is immediately won over and begins calling Kindaichi in to help him solve murders. He is quite aware that Kindaichi is going to solve everything while he just does the legwork, and he doesn't mind. Keep in mind that Kenmochi is a decorated police inspector, while Kindaichi is sixteen years old.
Kengo Akechi comes off as this as well, especially during his debut story arc. Arrogant and snobbish, he is an elite-level officer who often tries to compete with Kindaichi over murder cases. Despite their rivalry, however, there is a grudging mutual respect.
If Grevil de Blois wasn't one of these, he'd be a Noble Idiot With No Day Job. Luckily for him, Victorique is ready to play Holmes to his Lestrade...and the de Blois family is influential enough to make firing him awkward.
Some incarnations of Jim Gordon are this to Batman, particularly stories centered around Batman's early years.
Actually Gordon becomes this for the Pruitt building sequence of The Dark Knight, under the pressure he failed to heed Batman's correct instinct that the Joker had another trick up his sleeve. If it weren't for Batman the hostages would have been unintentionally killed by Gordon's orders. Gordon was also dealing with the fact that his son had been taken hostage by Harvey Dent, so that can't have helped.
Foley in The Dark Knight Rises. During the stock exchange robbery, he actually orders the cops to go after Batman, not after those four guys who were shooting up the stock exchange with submachine guns.
Recently, Batman's gained another: Edward Nigma, AKA The Riddler, who has (probably) reformed and is trying to use his fame as a villain to leverage a career as a detective. It hasn't gone well yet. At least one storyline has involved Batman and Nigma playing off each other, picking up tips.
Ashley Swift in The Maze Agency. She is actually a skilled detective, but not quite as good as Jennifer and Gabe, and tends to let her rivalry with Jennifer get the best of her.
In the first Sherlock Holmes novel, A Study In Scarlet, there were actually two such detectives (who were rivals between themselves): Tobias Gregson and the Trope Namer, G. Lestrade. It should be noted that Gregson didn't have many significant appearances again afterward, whereas Lestrade gets some bits of Character Development and often aids Holmes and Watson. Ironically, Holmes considers Gregson to be the smarter of the two. Watson later remarks in "The Red Circle" that while Gregson and the rest of the official police may be lacking in deductive skill, they are certainly not wanting in courage.
Also: Inspector Athelney Jones, in The Sign of Four and The Red-Headed League, and Inspector Stanley Hopkins, in several short stories.
And a subversion: Inspector Baynes in Wisteria Lodge is the one official police officer who at the end of the story is praised by Holmes (and justly, as he turned out to have also found the correct solution).
Averted with Stanley Hopkins, who respects and even looks up to Holmes's methods. Also, Inspector Martin of the Norfolk Constabulary in "The Dancing Men," was delighted to have Holmes' assistance upon learning that the murder victim was his client. Inspector Gregory, who worked with Holmes in "The Adventure Of Silver Blaze" was also warmly praised by Holmes for his investigative ability, lacking only the imagination to imagine what might have happened in a particular situation, and act on that intuition.
Likewise, Inspector James Japp for Hercule Poirot, though more so in the Poirot TV series than in the original novels by Agatha Christie. This is partly because the early Poirot stories, on which the series is largely based, followed the Sherlock Holmes pattern quite rigidly, with Poirot as Holmes, Captain Hastings in the role of Doctor Watson, and Inspector Japp playing the Lestrade part.
However, it needs to be said that Japp is a really competent inspector who solved many cases on his own (as evidenced by his appearance in Tommy and Tuppence). It's just that compared to Poirot, nobody is as competent.
Archie Goodwin functioned as a Lestrade for Nero Wolfe. However, Archie is smart and quick enough to connect most of the dots, to the point where he often figures out where his boss is going with a case before the last chapter (deliberately refusing to share it with the reader until Wolfe reveals the solution, of course). But he isn't really bothered when he doesn't: "That's why we keep a genius around here."
Inspector Cramer is also an example. He also counts as an Obstructive Bureaucrat. Wolfe points out that for 99% of murders, Cramer is better suited to the job. Wolfe is only needed for weird stuff.
Westman Block became this in Glen Cook's Garrett, P.I. series. Justified in that, when Block joined the force, it was more concerned with keeping the city free of riots and the lower classes off the Hill than with actually solving crimes: Block didn't really have anyone to learn proper detective techniques from.
M.C. Beaton's other Detective Agatha Raisin has P.C. Bill Wong, who's actually a pretty good Police officer. In Quiche of Death he actually works out who the murderer is at the same time as Agatha and ends up saving her when she gets in over her head.
The Lord Darcy story "The Bitter End" features a ClouseauExpy who grabs the Lestrade role in both hands. He's barely on the scene until he decides that because he can't see how the victim was poisoned, A Wizard Did It, and since Master Sean is the first wizard he encounters ....
In The Thrawn Trilogy, when Captain Pellaeon isn't being The Watson, he's the good type of this for the Grand Admiral. While he's observant and intelligent, he always comes to simpler conclusions. Thrawn always either overrides him or nudges him into seeing what really happened.
By the time Pellaeon is an Admiral, however, his strategizing has become much more like Thrawn's.
This has a lot to do with Pellaeon being as much Thrawn's protege as as his Watson.
In the Erast Fandorin story Leviathan, French detective M. Gauche plays the role. Until, that is, he does find the true killer and blackmails them to keep it quiet.
Gil (An ARM agent) does the real detective-ing, while a civilian detective story buff that knew the victim and suspect plays Lestrade. Gil recognizes the kid hoping for this trope to be true, but it's anything but.
In The Patchwork Girl, alhough technically the Moon is under UN and ARM jurisdiction Gil's still an outsider, and official detective work may interfere with his diplomatic duties. A Luna policewoman and the mayor's son share the role of Lestrade for this story, and Gil tags along behind them figuring things out.
Detective Sergeant (later Detective Inspector) Gilks in the Dirk Gently series, who is smarter than Dirk thinks he is, seems to have good instincts for cases that don't contain the weird crap Dirk's involvement invariably heralds, but who once decided that a man who was beheaded in a locked room was a suicide trying to be difficult.
When drawing all the comparisons between Sherlock Holmes and House, Dr. Cuddy most commonly fits the Lestrade role. She was a competent doctor, but spends most of her time behind the scenes in the administrative role. Add into that the fact that in the first seasons, House kind of tricked her into doing his boring legwork and the modern "I hate you, but you're too useful to fire" relationship commonly seen in adaptations today. His team fits this to a lesser degree.
Captain Leland Stottlemeyer for Monk. He's a competent policeman, but no match for Monk's skills, as he himself is quite aware.
Take the first two letters of Stottlemeyer's first and last name, along with that of his junior associate Randy Deacon (renamed Disher after the pilot), and you get a shout-out to Lestrade: Leland Stottlemeyer + Randy Deacon = Lestrade
Psych has Carlton "Lassie" Lassiter, who serves as this. There's a bit of a twist, in that Lassiter is a rather competent detective and often comes close to solving the problem on his own.
To some extent, FBI Special Agent Don Eppes with regard to his brother, Charlie, on NUMB3RS.
Lewis' 'weakness' is that compared to Morse he has a private life, and he's more "people" orientated then Morse's fact focus.
Lt. Columbo famously subverts this, although he himself would probably say otherwise.
Simon Brimmer in the 1975 adaptation of Ellery Queen. He is the host of a radio mystery series who fancies himself a real detective. He proves to have a knack for ferreting out useful information but always names the wrong person as the killer.
In the same series, the reporter Frank Flanagan will often try to beat the police to a killer. He too is invariably wrong.
Cabot Cove's Sheriff Amos Tupper (and later Sheriff Mort Metzger) in Murder, She Wrote.
In their defence, they're both incredibly competent in the day-to-day things a small town sheriff would have to do.
Jonathan Creek didn't have a recurring Lestrade character, but individual episodes sometimes had one. One example is Inspector Gideon Pryke from Black Canary, who like a good Lestrade spots several clues, impresses Jonathan by figuring out part of the case, but cannot solve it all himself.
Jonathan isn't quite a straight example, however, as the mysteries he gets roped into solving with various degrees of reluctance involve hugely elaborate feats of sleight-of-hand that even the very best detective would struggle to unpick... but Jonathan's day-job is designing such tricks for a stage-illusionist, so arguably his role is as much "highly specialist CSI technician" as "consulting detective".
In Sherlock, Inspector Lestrade is both a helpful, self-aware version of this trope, as well as being a Friend on the Force. The DVD commentary reveals that an important part of casting Lestrade was finding someone who the audience could believe would, if Sherlock Holmes did not exist, eventually solve the crimes on his own.
Various characters of this type show up throughout the entire run of Doctor Who.
In Elementary, Lestrade's role is taken by his friend and rival from the books, Tobias Gregson. In this iteration, his Friend on the Force status is heavily emphasised— he's the one who makes the arrests after Sherlock finds the bad guy, and always has police backup on hand for tough situations.
Gareth Lestrade was introduced in season 2 and he's a deconstruction of this trope. The show revealed how damaging this kind of relationship would be when the secret of Lestrade's success (Holmes) left suddenly and Lestrade still craved the limelight but didn't have the skills to back it up. He is disgraced and laughed off the force when a case blows up in his face and he falls back to his fame hungry ways after Sherlock solves the case for him.
Dojima is more Locked Out of the Loop than this trope. He manages to stay relatively close with the Investigation team on solving the murders with only about half (or less) of the clues and later on, he's the only cop still working on the case despite the fact that the rest of the department believes that it has been closed. A better example would be his partner, Adachi, from whom the main characters learn much about the police investigation (Dojima himself remains tight lipped about the subject) though it turns out that Adachi is the real killer and has spent the entire game misleading you.
In fact, his treating the matter as a murder at all casts doubts on his competence. You can't have a murder investigation without proof that somebody died, and non-medical personnel cannot legally declare someone to be dead unless the body is in pieces. So he was investigating a murder - and actually tried to arrest a man for that murder - without any evidence that a murder took place at all. This becomes especially clear at the end, where it is revealed that not only was there no foul play involved in what happened to the doctor, he hadn't actually died.
He also plays this role in the first game. Except that he's actually the villain in disguise, who is trying to cover up this own "murder," so it's not actually him. Even so, the disguised villain portrays him remarkably well, minus one telltale flaw that has nothing to do with this trope.
Sheriff George Woodman of Deadly Premonition is a no-nonsense guy who's pretty good at rounding up normal small-town criminal elements but is a bit outclassed when it comes to dealing with the outlandish murders that start occurring in his town. Or that's what he wants you to think...
Dol Grenn in Knights of the Old Republic II. In particular, he arrests the Exile, Atton, and Kreia for destroying Peragus—to be fair, a dark side Exile might be, but they still didn't create the circumstances that led to it. He would probably be more capable if he had the manpower he needed, a fact he laments. After it's cleared up, you can also get sidequests from him that consist of crimes that the security forces haven't been able to solve.
In Sherlock Holmes in the 22nd Century, a reanimated Sherlock is somewhat surprised (and perhaps dismayed) to discover that the head of Scotland Yard is a woman. More exactly, she's Beth Lestrade, a descendant of the original Lestrade he knew.