Changing Database Collation setting

Question

Hi,

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.

Thanks
Scott

Answer 1

This is not an easy task but the following article provides details on how it can be done...

http://support.microsoft.com/kb/325335

...note that I would make copies of your databases first and test thoroughly before applying this to production. Obviously back everything up first but an in-place change could be a risky proposition since it is not a simple task and it can lead to errors if the code expects a certain collation where it has changed.

Thanks

---

/Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem

Answer 2

Changing the database default collation is easy. But that will not change the table columns collation.

Here is how you change a single column collation:

http://www.sqlusa.com/bestpractices/changecollation/

The following article deals with collation issues:

http://sqlusa.com/bestpractices2005/collatedatabasedefault/

The following article has logic for automatic processing of all tables / all columns in a database:

http://www.sqlusa.com/bestpractices2008/searchalltablesallcolumns/

Dynamic SQL article:

http://www.sqlusa.com/bestpractices/dynamicsql/

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

Answer 3

thanks for the info...

i will take a look at the articles.