Fun thread. I'm sure some of these have been mentioned, but here are some of my drunk/high songs:
"Insane in the Membrane" and "Hits from the Bong" by Cypress Hill
"Dr. Feelgood" Motley Crue
Anything by Alice in Chains.
"rehab" by Amy Winehouse
"Beercan" by Beck
Those are just a few. I'm sure I'll start hearing my drinking/drugging themes now all the time.
Songs of sobriety? I haven't made that list yet. I guess Alice in Chains makes me want to be sober, since Layne and Mike are both gone as a result of their addictions.