I think the television show Dexter has the greatest opening theme.
Someone has posted it already about 14-16 posts above mine, so go there if you want to watch it.
Anyway, the theme is more than just the music, it's the images that convey the shows character. It shows a guy going through his morning ritual, but there is something violent and dark about every innocous action he performs. The theme in the show lets you know everything about the show, without doing anything blatantly obvious. That kind of visual is not used much today, and is sorely missed by those of us who can appreciate such things.