I did actually find the depiction of it in "Let That Be Your Last Battlefield" and TSFS to be the best. Even the laborious process of going through the senior officers to get the thing turned on... it really amps up the drama. I did find TNG's depiction of it equally well done in "1001001" and "Where Silence Has Lease", too.
But the trouble with a device like the self-destruct is that if it gets used too
often it just becomes a silly trope. It loses it's punch after a little too many uses, so (for example) when Picard tries to do it in "Nemesis" we just roll our eyes.