SQL SERVER – Change Collation of Database Column – T-SQL Script

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_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Change the database collation */
ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE 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.

SQL SERVER - Change Collation of Database Column - T-SQL Script colcollation

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)

, , ,
Previous Post
SQLAuthority News – Download – SQL Server 2005 Books Online (December 2008)
Next Post
SQLAuthority News – SQL Server Related Downloads from Microsoft

Related Posts

50 Comments. Leave new

  • hi sir this is gopi
    while intalling sql server 2005 i did not install sql server profiler now i want that profiler it is possible sir reply me

    Reply
  • how to restore the database with out mdf,and log files

    Reply
  • Hi Pinal, nice post as always. I have personally made a little script to help me understanding which columns i have to change in order to maintain the correct collation on a Database.
    Here’s the script if anyone needs something like this.
    NOTE: it doesn’t do nothing, but just prints the T-Sql instructions that allow to change the collation, this because based on my experience, i prefer to double check a couple of times before messing up with DB Settings :)

    DECLARE @collationType VARCHAR(100)

    SELECT @collationType = ‘Latin1_General_CI_AS’

    DECLARE collationToChangeColumns CURSOR
    FOR
    SELECT colonne.NAME ,
    ‘[‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + tabelle.NAME + ‘]’ ,
    colonne.SYSTEM_type_id ,
    colonne.max_length
    FROM sys.columns colonne
    JOIN sys.tables tabelle
    ON colonne.OBJECT_ID = tabelle.OBJECT_ID
    WHERE collation_name @collationType
    AND tabelle.TYPE = ‘u’

    DECLARE @tableName AS VARCHAR(100)
    DECLARE @columnName AS VARCHAR(100)
    DECLARE @typeID AS INT
    DECLARE @maxLength AS INT
    DECLARE @sqlString AS NVARCHAR(4000)

    OPEN collationToChangeColumns
    FETCH collationToChangeColumns INTO @columnName, @tableName, @typeID, @maxLength
    WHILE @@FETCH_STATUS -1
    BEGIN
    IF @typeID NOT IN ( 35, 99 )
    BEGIN
    IF @maxLength > 0
    BEGIN
    SET @sqlString = ‘ALTER TABLE ‘ + @tableName
    + ‘ alter column [‘ + @columnName + ‘] ‘
    + CAST(TYPE_NAME(@typeID) AS VARCHAR(20)) + ‘(‘
    + CAST(@maxLength AS VARCHAR(10)) + ‘) collate ‘
    + @collationType + ‘;’
    PRINT @sqlString
    END
    END
    FETCH collationToChangeColumns INTO @columnName, @tableName, @typeID,
    @maxLength

    END
    CLOSE collationToChangeColumns
    DEALLOCATE collationToChangeColumns

    Reply
  • eabidi syntax without CURSOR

    DECLARE @OldCollation VARCHAR(100), @NewCollation VARCHAR(100)
    SELECT @OldCollation = ‘SQL_Latin1_General_CP1_CI_AS’,
    @NewCollation=’Estonian_ci_as’;
    WITH XChange (XColumn,XTable,XType,XLen)
    as(
    SELECT c.NAME ,
    ‘[‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + t.NAME + ‘]’ ,
    c.SYSTEM_type_id ,
    c.max_length
    FROM sys.columns c
    JOIN sys.tables t ON c.OBJECT_ID = t.OBJECT_ID
    WHERE
    collation_name=@OldCollation
    AND t.TYPE = ‘u’)
    SELECT ‘ALTER TABLE ‘ + XTable
    + ‘ alter column [‘ + XColumn + ‘] ‘
    + CAST(TYPE_NAME(XType) AS VARCHAR(20)) + ‘(‘
    + CAST(XLen AS VARCHAR(10)) + ‘) collate ‘
    + @NewCollation + ‘;’
    FROM XChange
    where XType not in (35,99)
    and XLen>0;

    Reply
  • R.Chinnadurai
    March 20, 2014 7:23 pm

    Hi Pinal,

    How to convert the Arabic_CI_AS collation records into SQL_Latin1_General_CP1_CS_AS format using TSQL.

    Reply
  • EXCELLENT THANKS

    Reply

Leave a Reply

Menu