I thought Ellery Queen had the best opening credits ever. That cool playful riff over a chess board scattered with various kooky stuff.
The show itself? Don’t remember it.
The ABC TV show “Makin’ It” was canceled after only 9 weeks. The theme song debuted on the U.S. Billboard Hot 100 a short time after the show was gone, eventually peaking at #5.
I’m not sure if it qualifies as a “lousy show,” but the theme song for the U.S. release of Danger Man - Secret Agent Man - is much more well known than the show itself.
Airwolf did have two episodes that I would rate as some of the best episodic TV up to its day. Trouble is, it ran 80 episodes.
Ellery Queen is fine. Holds up OK.
Space 1999…well, there are some good episodes in there, there really are! But the rest? I offer these words: Joan Collins as the Queen of Space
As for the OP, I offer two:
Southland, great theme, nothing much special show
Without a Trace. One of the tightest, most intense 30 seconds of theme songs ever. While the show wasn’t “lousy”, it barely achieved mediocrity.
Honorable mention: 2 1/2 Men. The tightest and most intense two seconds of theme music ever! 
I got sick of Charlie, and the show shitting on Allen pretty quick.
The same thing happened in the early 1990s with a show on Fox called The Heights about a fictional band. The band recorded an actual song “How Do You Talk to an Angel” that hit number 1 on the Billboard Hot 100. The show only lasted three months before being cancelled due to poor ratings. I definitely would not call the song “great,” though.
Seriously we got 15 posts in before anyone suggested this!?! Surely the canonical example of the OP 
I had a huge crush on Catherine Schell, who only joined the show in season 2.
Never mind.
She’s not all that!
Yesterday I was wondering what my favorite TV theme song of a show I don’t like. And Greatest American Hero was the was the winner.
Peter Gunn.
Take your standard handsome-but-tough leading man straight out of Central Casting, and give him story lines straight out of paperback novels.
But then get Henry Mancini to do the theme song!
I was never much for Some Mothers Do ’Ave ’Em, but you have to hand it to a theme tune that spells out the entire title in Morse code - including punctuation:
If we’re bringing movies into this:
Angie was a forgettable sitcom, but its theme was catchy. “We come to each other from different worlds…” Coincidentally, its leads, Robert Hays and Donna Pescow, each were later in series (Starman and Out of This World, respectively), with the premise of a human woman and an extraterrestrial father having a child being raised by one of the parents.
Doesn’t fit the thread.Too Many Cooks is one of the greatest shows of all time. Who could forget the famous “Honey I’m ho-” scene?
My favorite song of all time is The Underdog Show theme song. The series was pretty bad.
The best music to the worst show: The Lone Ranger
Falcon Crest (a show I have never once considered watching) had a pretty boppin’ theme song by Bill Conti.
Was George of the Jungle a good cartoon? I have no idea, but I love the song.
I don’t think the Spiderman cartoon from the '60s is considered lousy but it sure wasn’t interesting to me. That theme song, though . . . Here comes the Spiderman
Same goes for Super Chicken by the same songwriting team.