X-Men, Batman and Gargoyles all have fantastic openings.
I always loved the fact the Game of Thrones opening showed the specific places we would visit in each episode.
Babylon 5 has a different intro each season, and the season three opening in particular hits really hard the first time you get to it.
Brisco County Jr had a theme that was so inspiring, NBC bought the rights to use it for the Olympics.