Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26

    Unanswered: Change database collation - quickly

    There have been several threads about changing a database's collation but none have come up with an easy answer before.
    The suggestion before was to create an empty database with the correct collation and then copy the data across.
    However this is hard work as you have to populate tables in a specific order in order not to violate foreign keys etc. You can't just dts the whole data.

    There follows scripts we have written to do the job. If people use them, please could you add to this thread whether they worked successfully or not.

    Firstly we change the default collation, then change all the types in the database to match the new collation.

    ===================
    --script to change database collation - James Agnini
    --
    --Replace <DATABASE> with the database name
    --Replace <COLLATION> with the collation, eg SQL_Latin1_General_CP1_CI_AS
    --
    --After running this script, run the script to rebuild all indexes

    ALTER DATABASE <DATABASE> COLLATE <COLLATION>

    exec sp_configure 'allow updates',1
    go
    reconfigure with override
    go
    update syscolumns
    set collationid = (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
    where collationid <> (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
    go
    exec sp_configure 'allow updates',0
    go
    reconfigure with override
    go
    ===================

    As we have directly edited system tables, we need to run a script to rebuild all the indexes. Otherwise you will get strange results like comparing strings in different table not working.
    The indexes have to actually be dropped and recreated in separate statements.
    You can't use DBCC DBREINDEX or create index with the DROP_EXISTING option as they won't do anything(thanks to SQL Server "optimization").
    This script loops through the tables and then loops through the indexes and unique constraints in separate sections. It gets the index information and drops and re-creates it.
    (The script could probably be tidied up with the duplicate code put into a stored procedure).

    ====================
    --Script to rebuild all table indexes, Version 0.1, May 2004 - James Agnini
    --
    --Database backups should be made before running any set of scripts that update databases.
    --All users should be out of the database before running this script

    print 'Rebuilding indexes for all tables:'
    go

    DECLARE @Table_Name varchar(128)
    declare @Index_Name varchar(128)
    declare @IndexId int
    declare @IndexKey int

    DECLARE Table_Cursor CURSOR FOR
    select TABLE_NAME from INFORMATION_SCHEMA.tables where table_type != 'VIEW'

    OPEN Table_Cursor
    FETCH NEXT FROM Table_Cursor
    INTO @Table_Name

    --loop through tables
    WHILE @@FETCH_STATUS = 0

    BEGIN
    print ''
    print @Table_Name

    DECLARE Index_Cursor CURSOR FOR
    select indid, name from sysindexes
    where id = OBJECT_ID(@Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
    not exists(Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    where TABLE_NAME = @Table_Name AND (CONSTRAINT_TYPE = 'PRIMARY KEY' or CONSTRAINT_TYPE = 'UNIQUE') and
    CONSTRAINT_NAME = name)
    order by indid

    OPEN Index_Cursor
    FETCH NEXT FROM Index_Cursor
    INTO @IndexId, @Index_Name

    --loop through indexes
    WHILE @@FETCH_STATUS = 0
    begin

    declare @SQL_String varchar(256)
    set @SQL_String = 'drop index '
    set @SQL_String = @SQL_String + @Table_Name + '.' + @Index_Name

    set @SQL_String = @SQL_String + ';create '

    if( (select INDEXPROPERTY ( OBJECT_ID(@Table_Name) , @Index_Name , 'IsUnique')) =1)
    set @SQL_String = @SQL_String + 'unique '

    if( (select INDEXPROPERTY ( OBJECT_ID(@Table_Name) , @Index_Name , 'IsClustered')) =1)
    set @SQL_String = @SQL_String + 'clustered '

    set @SQL_String = @SQL_String + 'index '
    set @SQL_String = @SQL_String + @Index_Name
    set @SQL_String = @SQL_String + ' on '
    set @SQL_String = @SQL_String + @Table_Name

    set @SQL_String = @SQL_String + '('

    --form column list
    SET @IndexKey = 1

    -- Loop through index columns, INDEX_COL can be from 1 to 16.
    WHILE @IndexKey <= 16 and INDEX_COL(@Table_Name, @IndexId, @IndexKey)
    IS NOT NULL
    BEGIN

    IF @IndexKey != 1
    set @SQL_String = @SQL_String + ','

    set @SQL_String = @SQL_String + index_col(@Table_Name, @IndexId, @IndexKey)

    SET @IndexKey = @IndexKey + 1
    END

    set @SQL_String = @SQL_String + ')'

    print @SQL_String
    EXEC (@SQL_String)

    FETCH NEXT FROM Index_Cursor
    INTO @IndexId, @Index_Name
    end

    CLOSE Index_Cursor
    DEALLOCATE Index_Cursor



    --loop through unique constraints
    DECLARE Contraint_Cursor CURSOR FOR
    select indid, name from sysindexes
    where id = OBJECT_ID(@Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
    exists( Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    where TABLE_NAME = @Table_Name AND CONSTRAINT_TYPE = 'UNIQUE' and CONSTRAINT_NAME = name)
    order by indid

    OPEN Contraint_Cursor
    FETCH NEXT FROM Contraint_Cursor
    INTO @IndexId, @Index_Name

    --loop through indexes
    WHILE @@FETCH_STATUS = 0
    begin

    set @SQL_String = 'alter table '
    set @SQL_String = @SQL_String + @Table_Name
    set @SQL_String = @SQL_String + ' drop constraint '
    set @SQL_String = @SQL_String + @Index_Name

    set @SQL_String = @SQL_String + '; alter table '
    set @SQL_String = @SQL_String + @Table_Name
    set @SQL_String = @SQL_String + ' WITH NOCHECK add constraint '
    set @SQL_String = @SQL_String + @Index_Name
    set @SQL_String = @SQL_String + ' unique '

    if( (select INDEXPROPERTY ( OBJECT_ID(@Table_Name) , @Index_Name , 'IsClustered')) =1)
    set @SQL_String = @SQL_String + 'clustered '

    set @SQL_String = @SQL_String + '('

    --form column list
    SET @IndexKey = 1

    -- Loop through index columns, INDEX_COL can be from 1 to 16.
    WHILE @IndexKey <= 16 and INDEX_COL(@Table_Name, @IndexId, @IndexKey)
    IS NOT NULL
    BEGIN

    IF @IndexKey != 1
    set @SQL_String = @SQL_String + ','

    set @SQL_String = @SQL_String + index_col(@Table_Name, @IndexId, @IndexKey)

    SET @IndexKey = @IndexKey + 1
    END

    set @SQL_String = @SQL_String + ')'

    print @SQL_String
    EXEC (@SQL_String)

    FETCH NEXT FROM Contraint_Cursor
    INTO @IndexId, @Index_Name
    end

    CLOSE Contraint_Cursor
    DEALLOCATE Contraint_Cursor

    FETCH NEXT FROM Table_Cursor
    INTO @Table_Name
    end

    CLOSE Table_Cursor
    DEALLOCATE Table_Cursor

    print ''
    print 'Finished, Please check output for errors.'
    ====================

    Any comments are very welcome.

  2. #2
    Join Date
    Mar 2004
    Location
    UK
    Posts
    26

    Adding error message so people find this topic when searching the internet

    The error "Cannot resolve collation conflict for equal to operation" may be caused by you doing a query between 2 databases with different collations.

    It can also happen if your database has a different collation to the server and you are using system stored procedures or tempory tables.
    See above for how to change your database's collation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •