SQL Server – Change Table Collation

Handy script to change the collation of all tables within a database. 

  • Change the database collation itself via the management console
  • Note that you’ll need to drop any indexes on the columns you are changing. I normally just script the indexes out, run this script and recreate the indexes

[code:tsql]

— =============================================
— Script to reset COLLATE to database default
— =============================================

declare @to_collation nvarchar(255)
select  @to_collation = ‘Latin1_General_CI_AI’

declare @dbname nvarchar(255)
set @dbname=db_name()
PRINT(‘ALTERING DATABASE ‘ + @dbname + ‘ TO COLLATION ‘+ @to_collation)

DECLARE COL_CURSOR CURSOR READ_ONLY FOR
select table_schema, table_name, column_name,
column_default, is_Nullable, Data_type,
character_maximum_length, collation_name
from information_schema.columns
INNER JOIN
(SELECT TABLE_NAME TN FROM
information_schema.tables where TABLE_TYPE=’BASE TABLE’ ) IT ON (TABLE_NAME=TN)
where (Data_type LIKE  ‘%char%’ OR Data_type LIKE ‘%text%’)
AND collation_name <> @to_collation

DECLARE @table_schema varchar(10), @table_name varchar(100), @column_name varchar(100),
@column_default varchar(100), @is_Nullable varchar(5), @Data_type varchar(100),
@character_maximum_length varchar(10), @columncollation varchar(200)

DECLARE @Execstr VARCHAR(2000)
OPEN COL_CURSOR
FETCH NEXT FROM COL_CURSOR INTO @table_schema, @table_name, @column_name,
@column_default, @is_Nullable, @Data_type,
@character_maximum_length, @columncollation
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
  SET @Execstr = ‘ALTER TABLE ‘ + @table_schema + ‘.’ + @table_name
    + ‘ ALTER COLUMN [‘ + @column_name  + ‘] ‘ + @Data_type
    
  IF @Data_type <> ‘ntext’ and @Data_type <> ‘text’ BEGIN  
   SET @Execstr = @Execstr + ‘ (‘
    +  case when @character_maximum_length = -1 then ‘MAX’ else @character_maximum_length end
    + ‘) ‘
  END
  
  SET @Execstr = @Execstr + ‘ COLLATE ‘ + @to_collation
        + CASE WHEN @is_Nullable=’no’  THEN  ‘ NOT NULL’ ELSE ‘ NULL ‘ END  
    
  
  EXEC (@Execstr)

  PRINT (‘Executing –>’ + @Execstr )
  PRINT (‘–Orig COLLATION WAS –>’ + @columncollation )

 END
 FETCH NEXT FROM COL_CURSOR INTO @table_schema, @table_name, @column_name,
 @column_default, @is_Nullable, @Data_type,
 @character_maximum_length, @columncollation
END
CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR
GO

[/code]

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *