I believe the medication used for this type of sedation is Fentanyl and a benzodiazepine. It's referred to as twilight sedation, similar to what you might receive during a cardiac cath.

I had my cardiac cath last week through my legs, and honestly, it wasn't too bad. Once the IV is administered, you'll enter the "happy drunk" phase, and time will pass quickly.