Change Collation of Database Column – T-SQL Script

December 20, 2008 by pinaldave

Just a day before I wrote about SQL SERVER – Find Collation of Database and Table Column Using T-SQL and I have received some good comments and one particular question was about how to change collation of database. It is quite simple do so.

Let us see following example.

USE AdventureWorks
GO/* Create Test Table */CREATE TABLE TestTable (FirstCol VARCHAR(10))GO/* Check Database Column Collation */SELECT name, collation_nameFROM sys.columnsWHERE OBJECT_ID IN ( SELECT OBJECT_IDFROM sys.objectsWHERE type = 'U'AND name = 'TestTable')GO/* Change the database collation */ALTER TABLE TestTableALTER COLUMN FirstCol VARCHAR(10)COLLATE SQL_Latin1_General_CP1_CS_AS NULLGO/* Check Database Column Collation */SELECT name, collation_nameFROM sys.columnsWHERE OBJECT_ID IN ( SELECT OBJECT_IDFROM sys.objectsWHERE type = 'U'AND name = 'TestTable')GO/* Database Cleanup */DROP TABLE TestTable
GO

When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.

Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.

Additionally, if you are looking for solution to SQL SERVER – Cannot resolve collation conflict for equal to operation visit here.

Reference : Pinal Dave (http://www.SQLAuthority.com)