It would have to be pretty bad, to where you couldn’t derive any listening enjoyment from its playback anyways. In a proper setup, the real risk to a stylus comes mostly from the meat between the system and the listening chair (that’s you and me).
Over the years I’ve had a stylus run over a couple of baked-on pieces of grit (hugely loud POP through the speakers), a few repeat /skip grooves, and a couple end grooves that didn’t “catch” the stylus and sent it running onto the label. No apparent damage from any of these cases. But certainly these are events you want to limit as much as possible. I’ve been fortunate enough to catch all cracked and chipped records via visual inspection before play - that could be disastrous. If it doesn’t look decent after a full clean and visual inspection, I don’t play it anymore.
The affixing of the stylus to cantilever often seems to be a weak point - this is a problem if you drop it hard into a record/platter, or snag it with a thread of clothing. The stylus itself should be quite durable with proper cleaning regimens.