Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Change the collation of Columns

By Srini Kolar,

Problem: Your SQL server instance is running in Latin1_general_100_BIN collation. You restore a database from testing or from different environment with different collation say Latin1_general_100_BIN2. You realize that the database should be in the BIN collation and not in BIN2. So you go to properties of the database in SSMS(SQL Server 2005 /2008) and change the collation under options section. Still you get error messages on equal operations whenever you run a query or Stored Procedure.

A little further investigation reveals that the columns of some of the tables are still in BIN2 collation. How do you change the collation of all the columns from BIN2 to BIN?

Tthe code provided first checks for the database collation and lists out all the columns that are different from database collation. Once you get the list of the columns, the script changes the collation of the columns to database collation.

Note: We cannot alter columns that are depended. Something like Foreign keys, Check constraints, and Clustered indexes implies Primary keys. How ever if you have too many columns in too many tables then this code would definitely help you changing the collation.

Total article views: 1899 | Views in the last 30 days: 6
Related Articles

Change Database Collation

A stored procedure to automate database collation change


Show all database's tables' columns' collations.

Created view displays all text columns' collations in the current database with the information whet...


Collation Changes

Collation Changes


Beware of Mixing Collations: Converting Collations

With SQL Server 2000 you are able to create databases or columns with a different collating setting ...


Change collation in SQL Serrver 2008 database.

Change collation in SQL Serrver 2008 database.


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones