I've been through this process before several years back. We changed our collation from Latin1_General_CI_AS to SQL_Latin1_CP1_CI_AS. It wasn't for any reason other than to make sure that everything in the instance was the same collation (due to temp tables and cross DB queries). Like mentioned in an earlier post the column collation change was the toughest part of the conversion.

In the end we wrote a script that dropped FK's, PK's and indexes, then updated the collations on each column that applied, finally adding all the FK's, PK's and indexes back in after the collation updates were done. There was a lot of dynamic SQL in that script the way it was written. However, this was all done with a SQL 2005 instance. Not certain if the old script would work in SQL 2008 or not.

We have a project coming up where we may have to do this again in SQL 2008, but we haven't had time to test.