Changing columns collation using batch sql in sql server 2005

I took over a databases. It appears that at some point default database collation was changed. As a result some columns have old default col开发者_JAVA技巧lation, but new columns, added after collation was changed have new collation. Also there's a great deal of stored procedure code that uses unions. When that code executes it happens that I get

Cannot resolve collation conflict for column 5 in SELECT statement.

error (for instance first SELECT returns column in Collation A, whereas second SELECT returns column in Collation B). Is there a way to write an SQL that would for instance select all columns with collation SQL_Latin1_General_CP1_CI_AS (old collation) to new collation Latin1_General_CI_AS ?

Thanks

Something like this should do the trick

• Look for all columns with incorrect collation

• compose an alter table statement & alter column statement per incorrect column

  DECLARE @sql nvarchar(4000) 
        , @tablename sysname
        , @name sysname
        , @datatype sysname
        , @length int
        , @precision int
        , @scale int
        , @is_nullable bit
  
  DECLARE cur_collations CURSOR LOCAL READ_ONLY
  FOR SELECT tablename = object_name(object_id)
           , name
           , TYPE_NAME(user_type_id)
           , max_length
        FROM sys.columns 
       WHERE collation_name = 'SQL_Latin1_General_CP1_CI_AS'
  
  OPEN cur_collations
  
  FETCH NEXT FROM cur_collations INTO @tablename, @name, @datatype, @length
  
  WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status <> -2)
        BEGIN
            set @sql = N'
              ALTER TABLE ' + QUOTENAME(@tablename) + N'
              ALTER COLUMN ' + QUOTENAME(@name) + N' ' + QUOTENAME(@datatype) + N'(' + cast(@length as nvarchar(10)) + N') 
              COLLATE Latin1_General_CI_AS
              ' + case when @is_nullable = 1 then N'NULL' else N'NOT NULL' end + N' '
            EXEC (@sql)
        END
      FETCH NEXT FROM cur_collations INTO @tablename, @name, @datatype, @length
    END
  
  CLOSE cur_collations
  DEALLOCATE cur_collations
  

Updated

• Supports Schemas
• Correctly implement is_nullable

DECLARE
    @sql nvarchar(4000),
    @tablename sysname,
    @schemaname sysname,
    @name sysname,
    @datatype sysname,
    @length int,
    @precision int,
    @scale int,
    @is_nullable bit

DECLARE cur_collations CURSOR LOCAL READ_ONLY FOR
SELECT
    tablename = OBJECT_NAME(columns.object_id),
    schemaname = SCHEMA_NAME(schema_id),
    columns.name,
    TYPE_NAME(user_type_id),
    max_length,
    is_nullable
FROM sys.columns
    INNER JOIN sys.objects on columns.object_id = objects.object_id
WHERE
    collation_name = 'SQL_Latin1_General_CP1_CI_AS'

OPEN cur_collations

FETCH NEXT FROM cur_collations INTO @tablename, @schemaname, @name, @datatype, @length, @is_nullable

WHILE (@@fetch_status  -1) BEGIN
    IF (@@fetch_status  -2) BEGIN
        SET @sql = N'ALTER TABLE ' + QUOTENAME(@schemaname) + '.' + QUOTENAME(@tablename) + N' ALTER COLUMN ' + QUOTENAME(@name) + N' ' + QUOTENAME(@datatype) + N'(' + cast(@length as nvarchar(10)) + N') COLLATE Latin1_General_CI_AS ' + case when @is_nullable = 1 then N'NULL' else N'NOT NULL' end + N' '
        --EXEC (@sql)
        PRINT @sql
    END
    FETCH NEXT FROM cur_collations INTO @tablename, @schemaname, @name, @datatype, @length, @is_nullable
END