SQL Clone
SQLServerCentral is supported by Redgate
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: 1948 | Views in the last 30 days: 2
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


Deep Dive into Changing Database Collation

Changing Database Collation Through Primary keys, Foreign Keys, Default and Check Constraints and mo...


Beware of Mixing Collations: Converting Collations

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