Change the collation of Columns

,

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.

/***
    The below script changes the collation of the columns
    to the database default collation. 
    Please note that the collation of the dependant columns, 
    like Foreign keys, Indexes and check constraints cannot be changed.
    For these columns we need to change it manually
    
***/
set nocount on

Declare 
    @SQL        varchar(max),
    @tablename    varchar(200),
    @columnname    varchar(200),
    @collationname    nvarchar(200),
    @typename    varchar(50),
    @maxlength    varchar(50),
    @precision    int,
    @scale        int

/********* Enter Database name here *****/
select @collationname = convert(nvarchar(200),(DATABASEPROPERTYEX('<<DatabaseName>>','Collation')))

/******** Declaring table variable ******/
declare @COLname Table 
(
    Collation_name    varchar(100),
    Column_name    varchar(200),
    Table_name    varchar(200),
    [Type_name]    varchar(100),
    isuserdefined    int,
    isassemblytype    int,
    max_length    bigint,
    [precision]    int,
    scale        int,
    [type]        char(3),
    iobjid        int
)
/******* inserting data into Table variable ****/
insert into @COLname
select    a.collation_name, 
    a.name as Column_Name, 
    b.name as Table_name,
    t.name AS TypeName,
    t.is_user_defined,
    t.is_assembly_type,
    a.max_length,
    a.precision,
    a.scale,
    o.type,
    i.object_id
from sys.columns a ,sys.tables b, sys.types t, sys.objects o , sys.indexes i
    where a.object_id = b.object_id 
    and t.user_type_id = a.user_type_id
    and a.object_id = o.object_id 
    and a.object_id = i.object_id
    and a.collation_name != @collationname    -- Checking for different collation
    and o.type not in('PK','P','FN','S')    -- Checking for depended columns
    and i.type != 1                -- Checking for indexes
    order by Table_name


while(select COUNT(1) from @COLname) > 0
    Begin
    select    TOP 1 
        @tablename = Table_name ,
        @columnname = Column_name,
        @typename = [Type_name],
        @maxlength = max_length,
        @precision = precision,
        @scale = scale
    from    @COLname
    select @SQL =    'alter table '+ @tablename +CHAR(13)+
            'alter column '+ @columnname +CHAR(32)+@typename+CHAR(40)+@maxlength+CHAR(41)+
            ' COLLATE '+@collationname+CHAR(13)
        Exec(@SQL)
        delete from @COLname where Table_name = @tablename and Column_name = @columnname
        
    end


---- End of the script

Rate

2.33 (3)

Share

Share

Rate

2.33 (3)