What're the most underused sprites in SMW levels, custom or not? I'd say Dino-Rhinos for original sprites, and for custom sprites, some Yoshi's Island sprites like the Fly Guy, and I also liked the Harry the Hedgehog sprite, though it is a bit of a hassle to use.
Vanilla: Magikoopas, Reznor, Dino Rhinos, Mini Rhinos, Yoshi's house birds, fireworks, Mecha-Koopas, green bubbles, and boss fireball (stationary)
Custom: anything that isn't n00b boss, other custom bosses, Phantos, or anything that came with sprite tool
I think the most underused SMW sprite is the Dino Rhino. I think it's because if you set SP4 to Dino Rhinos (GFX number 23 IIRC), it will only let you use Dino Rhinos, Dino Torches, and like nothing else for SP4 (Of course you still have SP3 sprites to use). A whole GFX file for just 2 sprites. That's sad.
The Giant Masked Koopa Boss is a pretty underrated custom sprite IMO. It's a really good custom boss. I even used it in my hack.
I think Dino rhinos (and Dino torches) are the most underused sprites that fit into all kinds of different settings and levels really well. It's a shame they aren't used more. Most of the other underused sprites are too situational (green gas bubbles, floating spike balls) or annoying (fishing lakitu, fishing boo).
I don't think the dinos of the smaller and big variations were ever used as often as they did (and I assume I even included them at some point in one of my hacks, but it was never fully released). As for custom sprites, probably those springboards that make you bounce diagonally and sideways.
Display Level Message 1 (Sprite 19) isn't used much since the Info Box (Sprite B9) can do that. The original sprite can only be used for the intro level but an ASM hack allows it to be used in every level.
The Flying Red Coin and the Flying Golden 1-Up are rarely seen in some hacks.
I've never seen the danmaku generator used in any hack besides Touhou Mario. As far as regular sprites go, what about the boo generator that was used in Donut Ghost House? I don't see that one used often, but then again I don't pay much attention to regular hacks nowadays.
I almost never find the floating mine (sprite #A4) in any hack I watched or played so far. I assume it has to do with it being quite easy to dodge since they are limited to the water they float on.
I noticed this years back and added some to my hack. The main problem is that they're too easy to dodge if they're already in water (as you said), and too hard to dodge if they fall from the sky. I had them fall in my level and it really seemed cheap at times, so I had to limit them.