You see the show for the first time, and the theme and/or music is freakin’ fantastic!…then you watch the actual show and it varies from “meh” to “I wonder if you can make eye wash out of battery acid?”. I’ve got some in mind, but out of kindness (and morbid curiosity) I’ll let y’all go first. 
The Big Bang Theory, although once in a while I have a good laugh despite the shallowness. Bare Naked Ladies really help elevate this show to more than it deserves.
It’s about time,
It’s about space…
It’s about two men in the strangest place.
.
Even as a wee lad, I recognized that here was the biggest letdown from clever opening theme to incredibly stupid show. I dare you to google and watch five minutes’ worth.
Ooooo, ooooo, no thanks. 
I was thinking more of this guy:
Fun Fact: After CBS bought the show, the name of Imogene Coca’s character was changed from “Shag” to “Shad,” for obvious reasons. Creator Sherwood Schwartz had apparently been unaware of the former’s use in British slang.
I was 11 in 1966, and I still remember Coca’s line in the pilot “Shag—nag!” when Joe E Brown complained about how she was treating him.
Missed edit: Ross, not Brown.
Here he is as “Gunther” in Car 54, Where Are You? where he was famous for saying “Oooo, oooo!”
OIP.q-aj_olVnPJpHqh3XKPZEQHaEK (474×266)
This picture didn’t want to stay embedded above:
https://i.pinimg.com/originals/40/81/2f/40812f20228531e8cb38e10bedeed75d.jpg
The Dukes of Hazzard has a pretty catchy theme song, but I don’t think I could sit through an entire episode nowadays.
I thought the OOOOOO was Horshack from Welcome back Kotter, which is a good answer to this question.
I would always sing along to the song for Hong Kong Fooey but never bothered to watch the show.
The TV theme songs of the 60s and 70s were epic masterpieces of exposition. TDoH is a prime example. Here are some of the best ‘this is what the show is all about’ themes (the relative lousiness of the shows themselves is debatable):
Gilligan’s Island
Come sit right back and you’ll hear a tale…
Brady Bunch
Here’s a story of a man named Brady…
The Love Boat
Love, exciting and new
come aboard. we’re expecting you…
Laverne and Shirley
…makin’ our dreams come true
Doin’ it our way…
One day at a Time
This is it
This is life, the one ya get…
Too Many Cooks:
It takes a lot to make a stew,
A pinch of salt and laughter too,
A scoop of kids to add the spice,
A dash of love to make it nice.
The theme to the G.I. Joe Movie rocks pretty hard and is the best part of the movie. The show was never high art but the movie took a hard right into weirdness and featured a race of Himalayan snake men who wanted to devolve humanity.
Cobra-La wasn’t a creation of the movie. The toy/animation canon got pretty weird too.
The best thing about Airwolf was its kick-ass theme music.
Too Many Cooks’ stew is made out of people! They’re making the stew out of people! Next thing they’ll be breeding us like cattle for food. You’ve gotta tell them. You’ve gotta tell them! Too Many Cooks’ stew is people! We’ve gotta stop them somehow!
Sigh, I know.
I thought the theme for Greatest American Hero was catchy, but the show was pretty stupid.
I love the 70s funk vibe of this intro. Probably never watched a complete episode.
I have the soundtrack album on vinyl; you’re right, it’s the best thing about the show. Even the origin story first episode was deadly dull. Never made it to epi 2.