I currently have a server with the collation set to SQL_Latin1_General_CP1_CI_AS.
However, some of the databases within the server are set to Latin1_General_BIN, probably because they were restored from another server some time ago. Also, even within the databases that have Latin1_General_BIN, some of the columns are set to SQL_Latin1_General_CP1_CI_AS, very confusing to say the least.
What i would like to do is change the database collation settings for these databases to match the server setting. I would also like to change all of the tables within these databases to have the columns also set to the server collation settings.
Is there a good way to go about this? I'm looking for the steps that i would need to take to make sure i don't mess anything up as these databases have there own sets of views and sp's that run each day.
1) Make sure you have good backups
2) Get downtime approved. You don't want users tripping you up
3) Test your backups, to make sure they work
4) Get scripts for each tables' indexes, unique constraints, primary keys, and foreign keys
5) Make sure that any unique or primary keys will still be unique after the change.
6) Check the backups again, to make sure they do really really work
7) Drop constraints, and indexes on one table (may require dropping foreign keys on other tables)
8) alter each column to the desired collation
9) rebuild any indexes/constraints dropped in step 7.
10) Repeat for all tables.
11) alter the database collation with the ALTER DATABASE statement.
12) Check in your code for any reference to the COLLATE statement (these may need to be removed).
13) Hug your backups (they deserved it)
EDIT: 14) Update index statistics for all tables.
I probably left out a thing or two. But you should really be certain that whatever you do, you can get back to where you started. Try this out with one of the databases in a test environment first, so you get a feel for what will go wrong.