tl;dr: Many of the big name Houses are, as you point out, not in great shape anymore. But there are more Great Houses than you probably realized, and not all of them are doing badly. A handful of them are on the verge of going extinct, but just as many are still going strong, and possibly improved their position recently.
If we use the typical definition of a Great House as being a House which has it's own vassal houses, then there are nine that still existed at some point during:
On the one hand, a couple of the Great Houses look to be getting along just fine, and if anything, have improved their position during the War of the Five Kings:
House Martell: In the books, Prince Doran has three children: Arianne, Quentyn, and Trystane, and by Dornish Law, Arianne is the heir to the throne, which I assume means she would keep the Martell name.
Quentyn, unfortunately, is dead by dragon accident.
In the show, they skipped the first two kids and went right for Trystane, who's heir to Sunspear. In both cases, he has been happily betrothed to Myrcella Baratheon, the King's elder sister, for most of the series.
House Bolton: Thanks to their alliance with the Lannisters, Roose Bolton not only gets promoted to Great House (supplanting the Starks and taking their former sworn houses), he also has a legitimate heir in Ramsay. As Ramsay is now married, the line should continue through him:
with Sansa Stark in the show, or Jeyne Pool (fake Arya Stark), in the novels; both have managed to escape at this point, however, so Ramsay may be out of luck.
If not Ramsay, then Roose's wife is pregnant, so there's another possible heir on the way.
House Frey: Walder Frey has a nearly endless supply of male heirs waiting in line, and there's already a second generation heir - Ryman Frey, son of Stevron Frey.
In the novels, Stevron's already dead, and his son is heir.
House Greyjoy: Their situation is currently sketchy; I initially put them in the "Bad Off" category, but I realized there's some things we don't know about Ironborn culture that could make difference. King Euron has no legitimate heirs, though he has plenty of bastards, but it's not clear how much that matters to the Iron Island. If he could legitimize any of his children by his salt wives, that would rescue House Greyjoy pretty nicely.
Theon, for his part, is no use: even if were still considered the heir to House Greyjoy, he won't be fathering any children in the near future. Asha/Yara's whereabouts on TV are unclear, but in the novel she is currently
married off to another House and held captive by Stannis.
so she's no help either.
Most of the Great Houses, on the other hand, have started falling apart. The list of these includes:
House Stark: No longer technically a House at all, the Starks are dead as far as the world is concerned. There is still a chance they could recover, though. There's suspicion that Robb may have legitimized Jon Snow before he died, and we also know that Bran and Rickon are both alive. If any of those three manages to survive the rest of the series and take back Winterfell, they could rebuild their House. Of course, this is Martin, so I wouldn't put money on it.
House Arryn: The heir to House Arryn is Robyn, who's frail and sickly and, IMO, not likely to survive very long in the real world. Petyr Baelish has managed to essentially supplant House Arryn as ruler of The Vale, as part of his plan to do whatever it is Littlefinger's really up to.
House Lannister: This one's in complete shambles, ironically enough. The Tywin branch of this family is basically over: Jaime, by law, cannot marry (he's a Kingsguard), Tyrion's been disowned and wanted for murder, and none Tywin's grandchildren are named Lannister. Kevan's branch isn't any better: two of his children are dead and the third is Lancel, who's basically renounced any claim to nobility. (This is far more explicit in the novel: he abandons his wife and forfeits his newly-acquired land to join the penitents.)
House Tyrell: is an interesting case: in the novels, they are doing just fine; on the show, they're on brink of extinction.
In the novels, three of Mace Tyrell's sons are alive and healthy, plus his uncle Moryn has several generations of children on his own. They're in no danger of dying out any time soon.
On TV, though, the character cutbacks mean that Mace Tyrell has only one son, Loras. While he's been released from his Kingsguard oath to Renly, it's debatable if he's ever going to produce an heir. His open homosexuality isn't really an issue: as Margaery proved with Renly, if there's a will, there's a way. The bigger problem is his betrothal to Cersei: she has every intention of getting out of that betrothal, and even if she can't, she's already proven more than willing to keep her husband childless out of spite.
That's assuming he's not killed by the High Sparrow.
Best case, IMO, Loras gets out of his betrothal to Cersei and finds someone else willing to "adapt" to his lifestyle long enough to have a son (or, alternatively, fake it like Cersei did with her children).
House Tully: Like House Stark, there is no longer technically a "House Tully", but they're in slightly better condition otherwise. The current heir, Edmure, is still alive and married, so there's still a chance for him to bear a son. Hoster's brother Brynden is also still alive, as far as we know.
Finally, in a bit of a class all it's own:
House Baratheon: As far as the "real" Baratheons are concerned, this house is on its last legs. In the novels, Stannis is making an attempt to take back the North and, from there, Westeros from the Lannister's allies, but he's already getting desperate. In the show,
this branch of the family line is all dead.
Legally, though, House Baratheon still rules in Westeros, as Tommen is officially Robert's son. We know that he's not, and we can probably guess that Cersei would be elated if she could find a way to use the Lannister name instead, but that's unlikely to ever happen. Being young, healthy, and married, the Baratheon line will probably continue through Tommen. After a few generations, rumors of his illegitimacy would become just a blip in their history -- assuming he's not remembered as the Baratheon that lost the Iron Throne.