How about something like "Planet Buster" from Alpha Centauri?
Basically, extremely costly missile with stupid amount of damage, which "terraforms" automatically as it hits. In bad way. Generally speaking, impact point hex turns to "coast", and all surrounding hexes are transformed to desert if they aren't underwater. Maybe a bit more sophisticated, taking mountains into account, I dunno.
Probably it should be built as a "National Unit" — only one in existence at once. Or not. And it may become available at Future Technology.
Another idea, unrelated to missiles, but related to mass destruction: "Doomsday Device" National Wonder. Build it in some city. Then, whenever you feel like, you can detonate it — either from city-screen somewhere, or maybe it actually can come out like a unit which can't move or fight. Detonation basically destroys a good half of your continent (turning anything into archipelago with small 1-hex islands), and ravages all other continents pretty badly (random destruction of land, new mountains appear from nowhere, apocalypse now).
Semi-scientifical explanation of phenomenon (no matter if it is not exactly correct from physics' standpoint, it is no more silly than GDR anyway): Doomsday Device is basically a hydrogen bomb of gargantuan size, stationary by its' nature. Basically some deep mine-shaft is flooded by heavy water, full of deuterium and tritium hydrogen. We are speaking about thousands of cubic meters of pure fusion bomb.
This hypothetical weapon may be built by crazy dictators who hate humanity and just don't want to lose and fail by themselves. Oh, thinking about that — it may reqiure full Autocracy tree to be completed.
I am not speaking that these ideas are awesome or even good from gameplay standpoint. Modular system of Civilization 5 is made so it can be possible to include anything player like, excluding anything he doesn't like. So these two unrealistic weapons may be excluded from your main mod to small complementation mini-mod, for example.