CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlcollation
dragonfly
dragonfly

Reputation: 17773

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 collation, 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

Upvotes: 1

Views: 5319

Answers (2)

Albert
Albert

Reputation: 1078

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

Upvotes: 0

Filip De Vos
Filip De Vos

Reputation: 11908

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

Upvotes: 4

Related Questions