The attached stored procedure will generate a change script that will recollate a database. Check the script comments for known limitations. You can run this script from query analyser.
Essentially this generates a change script that: drops all indexes, constraints and statistics on columns that don't have the desired target collation. Alters the columns to the new collation, alters the database to the new collation, and replaces all the indexes, constraints and statistics removed in the first place.
I'm attaching the file in the message reply window. I hope it works.