Supernatural is a fascinating show, but angst-heavy.
The gore is definitely there (stabbing, the occasional beheading, torture, gunshots, and a few characters explode. Two characters are mauled to death by hellhounds. Blood from the mouth occurs almost once an episode. Nearly every character has died (including the main characters), but death is by no means permanent in SPN.
There is around one sex scene a season (usually easy to skip over), and though Dean makes the occasional innuendo, it's a very mild part of the show.
Circa Season 7, Dean is quickly on his way to becoming an alcoholic, however this is only lightly touched on. Up until that point, drinking was generally casual and only in the background (though there were a couple instances of one or both brothers getting drunk).
Sam and Dean take the stage as the main characters: two brothers traveling across the country, hunting monsters. Sam is seen as the more empathetic one, while Dean plays the "bad boy" image (although he doesn't fit the stereotype as much as he'd like to think). They set off on a cross-country roadtrip, looking for their missing father. The storyline quickly becomes much more complex and develops into a multi-season arc.
I have to strongly object to the idea that there are no positive role models or messages in Supernatural.
It's certainly true that the Winchesters are far from saints, but they do everything in their power to protect the world (Sam winds up paying the ultimate price to protect it). Both boys have very self-sacrificing natures, as well as countless side characters who give their lives to help the boys. The Winchesters decision to keep fighting no matter how bad it gets is remarkable. They place a firm belief in free will and in doing what's right, regardless of the rules.
The show focuses on the bond between brothers and family becomes the main theme of the show. Especially adoptive family, as one of the characters says: "family don't end in blood."
All in all, the show has very strong, generally positive characters and an epic (there is no other word for it) story line, though the violence may be too much for some to handle.