In universe, I don’t know how useful saucer separation really was. Then again, I don’t really know how useful a floating city really was, either. It really showcased the hubris of the galaxy class design and the naivete of Starfleet at that point in time.

At the beginning of TNG, the Federation seemed very idyllic. That started to change with the introduction to the Borg, and was completely shattered with the Dominion War (remember the Jem’Hadar kamikaze pilots against a galaxy class, for example). At the end of the TNG era, you don’t really see many galaxy class ships flying around, but more ships that are more battle ready.

To your direct point about saucer separation, separating half the ship to leave vulnerable seemed like a bad idea. The saucer section (which had most of the population) didn’t really have a warp drive but it did have phasers. Still, it was susceptible to hit and run tactics while the lower portion was away.

Additionally, the saucer had most of the phaser array - that could be handy in a fight! Why leave that behind?

Lastly, you mentioned Generations. The saucer section couldn’t leave the lower section fast enough and was caught in the blast radius. The end result was the same as if traditional life boats were used - the destruction of the entire ship. In general, the separation procedure was slow. It made more sense to just take the saucer with you instead of wasting precious minutes in a separation procedure that could introduce the possibility of damage to the vessel before/after the time critical mission.

I’m not sure how useful saucer separation really was. Starfleet didn’t seem to think it was useful, either, as no other ship had that feature moving forward, and the one ship that was shown on screen to have it rarely used it.