i don't think its "written" so much as "designed". it basically has to give out the key information of cast and what sorta stuff to expect (Ie: picture of RDA, then RDA blasting some go'auld(did i spell that right?) away with a big gun, then a explosion/cool ship shot/etc). basically a mini-intro of sorts...some also go the route of explaining the premise...which i always find heavy handed, but neccessary sometimes. so key is being effective at relating the tone of the show by the opening sequence...like SG1 clearly is an action oriented show, BSG is more dramtic, while the family guy is whimiscal and fun...
this is what years of tv viewership has taught me...filmschool fails to match that...