There are only two tiers of lairs, as far as exploring go: normal and epic. Epic lairs are aifon isle, bradelines well, broken sepulcher and pyre of the seraphic. Everything else is a normal lair.
There are five types of results. BigBad, Bad, Neutral, Good and BigGood. Normal lairs can pop any of these, epic lairs only BigBad and BigGood. As HadesScorn said the higher the level of the unit exploring the higher the chance of a better result. It's still largely random however, to be certain to get good results you'd need a unit of at least level 74 (normal lair) or 54 (epic lair)!
For spawning units lairs on water use one list, lairs on land another.
BigBad: ALWAYS spawns a unit. With a good promotion. Plus henchmen.
Barrows can spawn skeleton/pyre zombie henchmen.
Ruins can spawn lizardman/gorilla henchmen.
Bad: Mostly bad promotions, sometimes units. Can kill level 1 units.
Neutral: Stuff like mutation, portals, catching the unit in a cage. Can spawn units.
Good: Mostly adds promotions, can also do stuff like give gold or spawn religion disciples. Some things (like disciples) disabled on water.
BigGood: Things like resources, golden ages, spawning friendly units (like great people). Some things disabled for water, some things disabled for land, mostly resources and units.
The chance that a lair is destroyed is based on the result. Generally speaking the better the result the bigger the chance of the lair being removed. BigGood has a 100% chance to destroy it.
All possible events are in CustomFunctions.py. The chance of the different types of results being triggered is in CvSpellInterface.py.