Nostalgia aside, you know who were the worst TV detectives despite being on a cool show. Crockett and Tubbs of Miami Vice.
They were
terrible police officers. They fucked up so big so many times and got so many innocent people killed it's not even funny.
In one episode they got a key witness against a drug lord and the witness is a recovering addict. So instead of putting a police watch on her or keeping in check with her, the drug lord just sends a henchman to break into her room while she's sleeping, he injects a fatal dose of heroin into her while asleep and she dies.
In another episode Crockett is building a case against a local drug dealer who's moving up. So he recruits a local kid who is a high school star football player and straight laced as can be. Somehow Crockett gets the kid to wear a frigging wire to try to get dirt on him, while in the limousine the kid is asking for the hard stuff and the dealer gets spooked and shoots him. The kid dies and the entire neighborhood is
pissed at Crockett for getting a kid killed by involving him in something he should have had nothing to do with.
Miami Vice still has dope as hell character arcs though.