You said, "is pretty much the end of the world in any sense how we know it." Certainly the use of nuclear weapons will have a dramatic impact on the world, however the use of a few low yield tactical weapons would not cause problems that cannot be contained in my opinion.
Further, you don't have to have a scenario persay where the President must launch nukes to make the "football" a necessity. For example, as I alluded to, our nuclear deterrent will lose all credibility if the person responsible for ordering a nuclear response can be cut off from that capability.
Personally, I think if we did use one on Iran (which there is zero chance we will do, but in theory) the rest of the Middle East (maybe except Syria) would talk in outrage about it, but quietly offer their support.
This is getting away from the issue, but with the current President, I think there is about a 0% chance of an American attack on Iran.
Battlefield nuclear weapons could decimate our conventional army. For example, a nuclear warhead detonated in space would seriously limit our satellite and military communications capability. Imagine a nuclear Iraq that would have been able to use tactical nuclear weapons against our forces in the massed in the desert prior to the 1991 invasion. That would have been devastating.