Severe fibromyalgia has given me quite a bit of familiarity with pain management.
Opiates:
IV Morphine - I've had it on five occasions, all in the ER for out-of-control pain. Completely overwhelming. I, too, would never use it unless in a hospital.
Percocet - The most effective of the oral pain meds I've tried.
Vicodin - Ineffective. All five of my ER visits were when I was being prescribed Vicodin, which was not controlling my pain at all.
Muscle Relaxers:
Carisoprodol (Soma) - I first got a prescription for it when a mild car accident set off a terrible, month-long flare that Percocet couldn't control. Effective, but not in a way that I like. It's super strong, the Everclear of muscle relaxers, and it dries out my throat to a terrible degree (and since I sing professionally, this is not good at all). My current doctors don't believe in prescribing it, and I don't miss it one bit.
Tizanidine - Mildly effective, but I couldn't get a boner while taking it.
Baclofen - Was just prescribed this today, and so far it's been more effective than Tizanidine and without the sexual side effects, but I need to take it for longer to render a verdict.
Cyclobenzaprine - I felt absolutely nothing from this.
Prescription Pain Cream:
Ketroprofen, Baclofen & Lidocaine - This was a custom compound from a specialty pharmacy that I received a week ago, and I had an allergic reaction to it (it made my throat swell). Since I'm tolerating the oral Baclofen (so far), it was either the Ketroprofen or the Lidocaine that I reacted to. Disappointing, since I got some decent pain relief from it, certainly way more than any OTC cream.
NSAIDs:
Acetaminophen - Contained in both Percocet and Vicodin, but I don't credit it with any real pain relief.
Ibuprofen - Only effective in 800mg doses, and even then not very much.
Naproxen - Awful. Nonexistent pain relief, terrible stomach inflammation.
Meloxicam (Mobic) - The best of the NSAIDs I've tried, though that's still not saying very much. Only effective when combined with something else.
Other Drugs:
Cannabis - Effective on the lower levels of pain, but not effective against strong flares. Since medical cannabis isn't approved yet in my state, I'm not using it anymore so that I can stay in good standing with my pain management docs.
Duloxetine (Cymbalta) - Very effective. My pain is always worst in my left arm, but Cymbalta has lessened my pain in my right arm and in my legs. Prior to Cymbalta, my pain was equally strong in all of my limbs. It hasn't made a dent in my left arm pain, and doesn't prevent strong flares, but has given me improvement in my quality of life.
Gabapentin (Neurontin) - Had a severe allergic reaction that falls under the category of "extreme emotional distress," but that's kind of soft-selling it. Almost immediately, it made me feel like I did when I was at the tail end of my first major depressive episode, when I was having very strange physical hallucinations (unexplained heat in my chest, feeling like my insides were melting away).
