-- =============================================
-- 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