After getting his sandtrout skin, he went on a spree to destroy and spill qanats, to supposedly undo the terraforming of Liet Kynes (the Demon of the Desert affair).
But in the Golden Path, he intended to terraform Arrakis, to make the worms go extinct anyway.
Or did I miss something?
His intention was to slow down the terraforming of Arrakis long enough to preserve a small number of worms in the deepest part of the Southern Desert. Without his intervention (and with the new Guild weather control satellites in orbit), the death of the worms would have happened far too quickly.
“Yes, My Lady. We of the desert see terrible things happening. The Little Makers come out of the sand as was foretold in the oldest prophecies. Shai-Hulud no longer can be found except in the deeps of the Empty Quarter. We have abandoned our friend, the desert!”
Children of Dune
Without Leto's drastic intervention, smashing the qanats and allowing the water to be reabsorbed by the sand, there wouldn't have been time to save any of the worms and the Guild wouldn't have been able to weather the 'lean times' that happened after the spice stopped flowing.
“Within one hundred years,” Ghanima said, “there’ll be fewer than fifty worms, and those will be sick ones kept in a carefully managed reservation. Their spice will be for the Spacing Guild only, and the price …” She shook her head. “I’ve seen Leto’s figures. He’s been all over the planet. He knows.”
Children of Dune
For the record, his plan wasn't to make the worms go extinct. Even with his intervention, the change is, at this point, largely inevitable. All he can do is delay it.
