Tuesday, March 06, 2012 8:21 PMHi,
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.
Wednesday, March 07, 2012 1:09 AM
This is not an easy task but the following article provides details on how it can be done...
...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.
/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
Wednesday, March 07, 2012 3:35 PMAnswerer
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:
The following article deals with collation issues:
The following article has logic for automatic processing of all tables / all columns in a database:
Dynamic SQL article:
Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
- Marked As Answer by KJian_ Tuesday, March 13, 2012 2:57 AM
Wednesday, March 07, 2012 4:48 PM
thanks for the info...
i will take a look at the articles.