Yoshi grows wings and takes off. In this state he can fly freely, and deals minor damage (8% in Brawl, 3% in Smash 4) to opponents by simply touching them. Pressing either attack button makes Yoshi shoot high-knockback fireballs that do about 17% (14~18% in Smash 4). In Brawl the fireballs immediately explode on contact with a player, while in Smash 4 they can pass through multiple players. If no buttons are pushed for about three seconds (two seconds in Smash 4), Yoshi will start to breathe a continuous stream of fire which causes very heavy damage (up to 66%), (30% in Smash 4). This Final Smash lasts about 20 seconds, and Yoshi is invincible for its entire duration.
This move is based on the various powers that Yoshi could obtain in Super Mario World by holding Koopa shells in his mouth, depending on the color of the Koopa shell. Holding a Blue Shell gives Yoshi wings and allows him to fly, holding a Red Shell allows him to spit it out as fireballs, holding a Yellow Shell makes him create mini-earthquakes each time he lands, and holding a flashing shell allows Yoshi to do all three. Super Dragon is presumably based off of this last effect, although the mini-earthquakes are not present and Yoshi can breathe fire or spit fireballs as many times as he wishes while the Final Smash is in effect. In Super Mario World, Yoshis of a color other than green gain the power given by the corresponding shell color while holding any shell; this does not happen in Smash.
The name comes from Super Mario World as well: In the Japanese version the message block at Yoshi's House is signed "Super Dragon Yoshi"; this is changed to simply "Yoshi" in localized versions. In the GBA remake, once the game has been completed the message block changes to one signed by "Super Dragon Yoshi" in all versions.
Yoshi with wings on his back that give him the power to fly freely across the sky. In this state, he can spew giant fireballs. He is more mobile and powerful, but the inertia makes movement quirky, so it takes some skill to master flight. Yoshi returns to a normal state after a set period of time has passed.
Wings sprout from Yoshi's back and he transforms into a super-cool dragon! While transformed, Yoshi is invincible and will constantly breathe fire. He can also ram into foes and shoot fireballs at them. The fireballs are great for launching foes and most powerful when used at close range.
A pair of wings appear on Yoshi's back, turning him into an angelic dragon. While the Final Smash is active, he can fly freely through the air, and is invincible to boot. He constantly breathes fire, can ram into opponents, and can also shoot fireballs that deal a lot of damage and have great launching power, especially at close range.