That side of the Cape is mostly all sand. Not too much to see.
There is a shop in Barnstable (The Dive Locker
http://63.74.120.153/divelocker/) and one in Plymouth (Divers Market
http://www.diversmarket.com/) I live right nearby Divers Market and have always been happy with them. I don't have much experience at all with the Dive Locker so I can't say. For charters, the biggest operation in the area is Cape Ann Dive in Rockport (
http://www.capeanndivers.com/) or you can try Easy Diver (
http://www.easy-diver.com/) or Flounda Pounda. (
http://www.coastaldivecenter.com/index.htm) I've gone out with all of them and each has it's good points. I love diving on the south shore, others swear by Gloucester/Rockport. CAD is the most commercial of the operations but goes to some great sites. Easy Diver is more laid back and generally keeps shallower if they don't know you. The captain is a bit of a throwback to an earlier age but I really enjoy him. Flounda Pounda is a realtively new operation. I've only been out with him once and had a good time and will go again. He is on the south shore, closer to the Cape, the others are a good 2 hour drive away. If you want to do tech diving, try Northern Atlantic Dive Expeditions (
http://www.northernatlanticdive.com/) another class outfit on the north shore.
As far as what you need, that's a loaded question. Wetsuit requirements are really based on your idea of cold. Most folks up here dive a 7 mm with hood, boots ands gloves. At least on the North side of Cape Cod and assuming you go deeper than 30-40 feet. South and shallow you can get by with less, but you will always need the hood, gloves and boots.
As far as things to do in the area, Falmouth is a great town, Woods Hole is neat. A court order couldn't get me to go to Hyannis in the summer, I'd rather stick needles in my eyes. The Cape Cod baseball league will be in full swing then, tomorrows big leaguers today, and it's free. Plymouth, MA is 1/2 hour away, come see some Pilgrims and our world famous caged rock.
Paul