I am currently testing migrating a database from SQL 7 to SQL 2005.
Following a backup/copy/restore to the new server, the database now has a backwards compatibilty collation of SQL_Latin1_General_CP1_CI_AS.
I have changed the database itself to be Latin1_General_CI_AS but (as expected) the collation at a column level remains SQL_Latin1_General_CP1_CI_AS, so this needs changing.
I found a nifty script on this website (LM_ChangeCollation) which uses a cursor to go through all the tables and do a Alter Table Alter Column Collate.
Except it doesn't work :-(
It appears that if you have any sort of constraint it fails with the following message:
"Msg 5074, Level 16, State 1, Line 1
The object 'MY_CLUSTERED_INDEX' is dependent on column 'my_column'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN my_column failed because one or more objects access this column."
So, Anyone got any advice on how to change collation on columns where you have constraints without any major pain?