Changing Collation on Columns

Had a problem with this after importing data from one database to another using “SELECT INTO” over a link. A co-worker developed this, so kudos go to him…

declare @table_name varchar(50)

declare @column varchar(50)

declare @datatype varchar(50)

declare @nullornot varchar(15)

declare @length varchar(3)

declare @getprops cursor

SET @getprops = CURSOR for
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CASE WHEN IS_NULLABLE = ‘YES’

   THEN ‘NULL’

   ELSE ‘NOT NULL’

   END AS NLL

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = ‘Metrics’

 AND COLLATION_NAME = ‘SQL_Latin1_General_CP1_CI_AS’
OPEN @getprops

FETCH NEXT

FROM @getprops into @table_name, @column, @datatype, @length, @nullornot

WHILE @@FETCH_STATUS = 0

BEGIN
PRINT N’ALTER TABLE ‘ + @table_name + N’ ALTER COLUMN ‘ + @column

PRINT @datatype + N'(‘ + @length + N’) COLLATE SQL_Latin1_General_CP850_CI_AS ‘ + @nullornot

PRINT N’GO’

PRINT N”
FETCH NEXT

FROM @getprops into @table_name, @column, @datatype, @length, @nullornot

END

CLOSE @getprops

DEALLOCATE @getprops

GO