I generally prefer short and sweet opening credits. It doesn’t get much better than Curb Your Enthusiam - most episodes only use the first 7 seconds, and those that play the entire song incorporate the other 23 seconds into the opening scene.
Nothing made me want to rip my ears out more than the theme song from The OC. Gilmore Girls was painful to sit through more than once.
Favorite: Murphy Brown
Least Favorite: the new, very grating version of the Sesame Street theme
Going with some ancient ones:
Favorites:
Hawaii 5-0
Rawhide
Hated:
Brady Bunch
**Welcome Back, Kotter. **
Catchy, classic 70s pop song that was actually a no. 1 hit. By John Sebastian of the Spoonful
I really like the opening theme to Penn and Teller’s Bullshit. Teenage Mutant Turtles, also good. Captain Planet. Inspector Gadget.
I can’t believe I forgot The Golden Girls. I watched the entire series as a marathon over a few months early last year and I can’t remember ever skipping the intro.
Favorite: I doubt anyone else remembers Glory Days despite the fact that Poppy Montgomery, Emily VanCamp, and Eddie Cahill were all in the main cast, but it has my favorite ever theme song - “Excess” by Tricky.
Least Favorite: Party of Five’s “Closer to Free” by the Bodeans. I hated that one even more than One Tree Hill’s.
I quite like the music composed by Howard Goodall for the British sitcom, Red Dwarf.
[ The Spacewalk tune incorporates The Blue Danube into the theme… ]
I always liked the Growing Pains theme song.
The Wonder Years had a great song (Joe Cocker singing With A Little Help From My Friends)
The Psych theme song is perfect for the show, and they like to play around with it sometimes and play alternate versions in different languages or styles.
I like Cake, so I always enjoy the opening of Chuck.
One that’s surprised me by becoming a favourite (because I’ve never really liked the programme, much) is the theme for Murder She Wrote.
It seems to encapsulate the character of the show neatly, and it’s very nicely orchestrated.
Boardwalk Empire. The Rescue Me and Wonder Years theme songs are also frequent on my playlist.
Great: The Man from U.N.C.L.E.
Not so great: Small Wonder
I second the Twin Peaks theme. I recommend the soundtrack CD.
I also cast one vote for The Andy Griffith Show.
Me too, I think Penn had said they won an award for their opening sequence in the Bible episode.
Favourite: Firefly, by a mile. In fact, when I ran out of Firefly to watch, one of my sad thoughts was “but now I’ve got no reason to listen to the theme saong any more!”
Least Favourite: I’d go with Seinfeld, but it’s probably just because I can’t stand the show in general. Go with Get Smart as a second choice.
Another vote for Firefly! I listened to all of Troubles links, and liked them all, aside from the damn Firefly theme. 
Alfred Hitchcock Presents is a memorable intro, though I would say I like it.
Hated The Facts Of Life,
but liked Diff’rent Strokes
Also the “Life” theme was cool, (trying to get a little abstract.
edit: One Foot In The Grave
Did you know that not everyone gets the same version? I think that in Sweden we get the second one from that video but now I’m not sure.
Hmm, I’ve never noticed before but it really, for some reason, reminds me of the intro to Metroid Prime:
My top three are Big Bang, Twilight Zone (which have been mentioned) and Hogan’s Heroes (which has not).