It might help if you clarify what you mean by drug use and abuse -- are you talking about illegal drugs, or legal substances? What do you mean by abuse? Using more than moderation would indicate? Being addicted?

Regarding Sherlock Holmes particularly, Arthur Conan Doyle was a medical doctor. Cocaine wasn't illegal, but Doyle was a little ahead of his time in his views on its addictive properties, so Holmes is thoroughly addicted to cocaine for a period of time in the stories but many readers would not have reacted the way we would today to that in the stories.

Opium use -- Holmes sometimes appears to use opium as well, but it's hard to be sure if he's addicted, as just as often he appears to be acting while following someone -- was seen as something that mostly foreigners did, particularly Asians. Opium dens were like dive bars -- disreputable places you wouldn't want to meet someone you knew. You can see that cultural reaction recorded in The Picture of Dorian Gray: Gray is a respectable upper class guy, and he scandalizes people by going to opium dens, following the sort of philosophy presented in Huysman's A Rebours ("Against the Grain"), where the main character withdraws from society and tries to create a perfect hedonistic world in his country estate. The book was very popular in Britain at the time, and it became the focus for a kind of sub-culture pursuing pleasure. So the drug use surrounding that sub-culture was a bit like marijuana use today -- often it's not the drug itself, but the perceived sub-culture surrounding it, that bothered people.