Thanks for the kind comments everyone.

This question was written after I'd spent a day or so changing the collation on a database and found that before I could even change the DB collation I had to drop all the computed columns, check constraints and un-schemabind any schemabound functions and views.

As to then changing individual columns, I had to drop/disable indexes and foreign keys, change the collation on the columns and then rebuild everything. All quite labour intensive.

The reason I did it was because I'd restored a SQL collated database with some SQL and some Windows collated columns to a Windows collated server. It was then complaining about stored procedures which created temp tables and joined them to existing tables on character columns. I assume that the collation in the tempdb must have been the same as the server collation, which would explain the problem. The mix of collations (I've no idea how it got like that by the way) had always bugged me in the past so I decided to sort it out once and for all.

If anyone is interested, Erland Sommarskog has raised a suggestion on Connect to make it easier to change the collation on a database.