I've dove a week at all of them since about 2005. Here's my
subjective opinions on each:
Bonaire is all about small stuff. Lots of macro, seahorses, frogfish, eels etc. Bari Reef is the site with the most counted species of fish in the Caribbean. It's very easy shorediving as good, named dive sites are in some areas (esp. south) literally 500' apart. Shorediving is as easy as getting tanks from your resort, loading the truck and driving a few minutes to a dive site. The sites are close to and parallel the shore. Entries can be a little trickier as a lot of them are over ironshore or coral rubble. Downside is that IMO after a week, it all starts to look a little similar. There are enough boat/shore dives to keep things interesting, not a lot of really vertical walls and about the biggest thing we saw all week was some tarpon and lots of smaller turtles. Nightlife is pretty limited to a few bars downtown or at the dive resorts. I haven't been to Bonaire since the cruise ships started arriving, given the size of downtown I don't think I'd want to be there then.
Some good restaurants also. There are clusters of dive resorts in two main areas that all have either good house reefs or are on named dive sites. There are some really good dives there, Salt Pier, Invisibles, Rappel, Forest, others. It's easily possible to do 4+ dives per day at multiple sites due to the high concentration of dive sites both north and south of town within a few minute drive. Facilities at any of the non-resort affiliated dive sites are non-existent - bring everything needed. And don't leave anything in the truck, there is some minor theft at remote sites.
Curacao is a bigger version of Bonaire in a lot of ways. It's on the same reef although IMO it's more diverse in some areas. Curacao to the west is more vertical but there were also locations where if dropped on the reef you couldn't tell it from Bonaire. We saw much the same wildlife except for many more larger eels. There is probably slightly more current overall than Bonaire but nothing that wasn't manageable, we had to slightly alter our plans several times because of it. Shore dive sites there are farther out so longer surface swims are required to get to the reef. On the west side, there is some vertical ironshore cliffs, breaks in it are often sandy beach coves that you swim out from. Due to the greater distances between sites and that most are short drives off the main road, it's harder to do as many dives as Bonaire unless you double up at sites. Diversity is about the same except we often found more interesting things per location, at one we found a wreck at 80', seahorses at 40', squid under the divedock and turtles in the shallows. Many of the better, known dive sites have independent operators on-site for tank/wt. rentals etc. And they have the Dive Bus if you're looking to do some escorted shore diving.
Curacao on the surface has much more of everything. Nicer resorts - Marriott, Hilton, etc, a large variety of apt./condo style places. Not as many are on the water as the dive resorts on Bonaire. There are some night clubs and about a dozen casinos downtown and at various hotels. It's also a large cruise destination, the ships dock downtown so the shopping district can be correspondingly crowded. Since it's also a deepwater port and there's an oil refinery, it's somewhat more commercialized. Some good restaurants oceanfront as well. It's also large enough to have the U.S. chains, aka McDonalds, Denny's etc. It also seemed less safe, there was a lot more security evident, most resorts were barred/gated, although we didn't have any problems, even one of the apt. mgrs. we stayed with mentioned hiding/locking valuables at some of the western sites.
ShoreDiving.com - Your Shore Diving and Snorkeling Web Community! has pictures of the dive sites for both Bonaire and Curacao so you can see the differences.
Grand Cayman (I've not been to the Sister Islands) is much more upscale - and expensive. I haven't been there in about 6 years so some of this may have changed. The diving there is among the best for diversity. Deep walls, large pelagics, schools of tarpon, eagle rays, stingrays, turtles were the norm on most of our dives. The north wall is pretty accessible, even from shore in some areas and it really drops fast. A lot of first dives begin down the wall at 100' or so but there's also plenty of shallower stuff including some wrecks. I spent an entire dive at Aquarium and didn't exceed 40', full of macro stuff, cleaning stations etc. There are so many good dives it's hard to name them all: Trinity Caverns, Hepps Wall, Orange Canyon, Big Tunnel all make my short list. And there's Stingray City, it's fun to do once. Lots of turtles around the Turtle Reef/farm area, it's their country's mascot.
There are some excellent restaurants and several upscale clubs. Everything on Cayman is very expensive, probably the most expensive of the 4 locations you're considering. The CI$ is negative to the U.S. dollar so your money is worth about .80 on the dollar. Once you get over paying $30-50 for dinner it's a really nice, very clean island. With about 400 banks on the island, it seemed really
safe also. It's not really a dive-specific destination, with the exception of Sunset House and Cobalt Coast, it's more a resort location with great diving. There are many upscale chain resorts along SMB, depending on your party size, we've found it best to rent condos. Most dive operators will pick you up along Seven Mile Beach for boat dives, but the reef there is too far out in most places for shore diving. However it's really an excellent beach area. There are several really good shore dives there, most have on-site operators and won't let you remove tanks from their property. Georgetown is fairly small and they get a lot of big cruise ships so downtown is someplace to avoid when they're in.
We recently did the T/C Explorer so my experiences are limited to the diving and about a day total spent on Provo coming/going. The diving there is similar to Grand Cayman, lots of deep walls and major coral formations with a lot of large pelagics. We dove off Provo, then moved to West Caicos and finally to French Cay. FC may be the best diving I've done in the last five years, coral on coral all the way down the wall. So many stingrays it was boring, turtles on most dives, sharks on many, barracuda, rays, lots of conch, crabs, lobster and at many sites fish nurseries surrounding the coral heads. It's less visited the farther out you go so the animals are correspondingly less "trained" - I liked that. We only spent a day and night on Provo, Grace Bay was interesting, had some bars/nightlife and many good restaurants. Also some expensive shopping areas. There's at least one casino, I was there, pretty small. Nice, quiet, clean island - I've read Grand Turk is much smaller.
hth you decide, PM if you have other questions, I may not follow this thread.