I don't see why Yzerman would want to leave Tampa to come to Detroit. He is building his own team around some great pieces that were there when he took the job (Stamkos, Hedman, St. Louis who he traded for a nice return, etc...). If he came to Detroit, it would just be as Holland's successor. It would eventually have Yzerman's fingerprints on it, but at this point, it would be keeping the Red Machine running, as opposed to a complete rebuild job like he has been doing in Tampa. When Holland moves on, and likely not for a while, it will likely be someone promoted internally to the GM role.
It has taken Holland a while to establish this team as his own, since many claimed over the years he just maintained what Devellano built or was lucky to have Scotty around and calling a lot of shots. Then in 2002 it was that he "bought" the Cup with Hasek, Hull, Robitaille, etc... and loaded the team he had in place with more HoF caliber talent. I think how well he managed this team into the salary cap era and to the 2008 Cup have proved his caliber as a top GM, but there are still people that want to give more credit to Nill being a great assistant GM and a keen eye for talent and Hakan Andersson for his efforts in scouting Europe.
Maybe Yzerman, due to his glory in Detroit as a player, would get more credit than what some fans want to give Holland, but I can see Yzerman just wanting to build his own legacy as a GM, rather than taking over a team that has been so well run over the years.