Yes, many years ago I had a colonoscopy like that, and I got to watch while just lightly sedated. I actually found it rather interesting and entertaining (no doubt the light sedation enhanced that).
I had one about a year ago, and that time I was pretty much "out", not remembering anything. Disappointing, LOL.
The takeaway here is, I think, that you can request to have the lighter sedation of you're nervous about something heavier, and it really shouldn't be painful at all.
Really, the worst thing about the procedure is the prep--having to drink all that nasty bowel cleanser.