Yesterday I hade the unfortenate task to change the database collation for a particular database.
Not only the default database collation should be changed, but also all columns!
After some investigating about how to do this, I noticed that check constraints and foreign key constraints must be removed before changing a collation. Also indexes needed to be removed for this operation to succeed.
I did it manually since it was my first time, but I wrote down the steps and today I gathered the notes into a beta-script, just in case I need to do this again in the future
Why a script? Because most common given answer to this task is to create a new database with the correct collation, bcp out all data and import all data in the new database. Then there is the hassle involved to drop the old database, change name for the new database, change logical and eventually the physical file names. A lot of work even with this approach.
So, here is the script http://developerworkshop.net/Scripts/Collation.sql and please post back or email me any comment you have about this script.
PS. You use the script on your own risk and I make no guarantee that it will work 100%. It is still in beta.