I've had 3 now plus the lower one sigmoidoscopy think it was called. As others have said, the bowel prep is by far the worst bit. Thought the drink was horrible, but for me on my recent one, the worst bit was seeing some pill sized round stone like things in the toilet after flushing! I managed to rescue a few and put in a sample bottle to take with me. The camera guy wasn't interested in the slightest, but the colo-rectal consultant drew a diagram of the bowel and colon and explained that this was what was called diverticulitis, The bowel prep had done its job extremely well, and brought the residue out of the pockets in my bowel/colon, and apparently these are what causes the pain of diverticulitis!
Be warned, you probably won't just have one visit to the loo with bowel prep, think I had to go about 4 times, and ended up with just loads of clear water coming out.
The procedure itself is more embarrassing than anything else. I had no anaesthetic, but gas and air was offered. Didn't think it at all painful, just slightly uncomfortable.