What is actually involved in consecrating a Catholic church? Does a priest or bishop just come and say a prayer over/towards the building? If so, what is the prayer? Are there any liturgical gestures or motions that the priest/bishop has to do? I'm interested in the details
The solemn ceremony of dedication, or consecration of a church is found in the Roman Pontifical and is performed de jure by a bishop. The simpler rite, which is given in the Roman Ritual, is generally reserved to bishops, but may be also undertaken by a priest with episcopal delegation. I know of cases where a priest have re-consecrated a Catholic Church after the church had been desecrated with permission of the local ordinary.
The whole ceremony can be read in the Dedication of a Church and an Altar from the Sacred Congregation for the Sacraments and Divine Worship. It is too long to go into all the details here, so I will leave it here where one can peruse this at one's leisure.
The main highlights that one should be aware of is the actual Prayer of Dedication and the Rites of Anointing, Incensing, Covering, and Lighting the Altar.
You can see Cardinal Seán O'Malley presiding at the Mass of Dedication of the new Saint Bonaventure Church in Manomet, MA. in this YouTube video: Dedication of the New Saint Bonaventure Church.