I don’t agree with the sentiment that nobody wants to come to Detroit. Detroit is the 12th largest media market in the NHL and an original six team. It is also home to many NHL players. It is a town known for an intensely supportive fan base. If you are a free agent and your choice is between Detroit and Ottawa does anyone really think that players will flock to Ottawa? Columbus? Calgary? Raleigh? Buffalo? Winnipeg? Maybe some prefer NYC, Vegas, LA. Maybe some prefer no tax states. But these guys want to get paid, they want to play, and they want to compete. Detroit wasn’t competitive five years ago or even three. They are on the cusp now.
Also, maybe I’m forgetting someone but the only player I remember using a NMC to veto a trade to Detroit was Tyler Myers who apparently has a child with special needs, who was born and has family in Texas, and who ended up in Dallas. Faulk had a 15-team no trade clause, so either Detroit was not on it, or he willingly waived it.