Update Collation of all fields in database on the fly

Question

We recently moved our database from our SQL Server 2005 server to our SQL Server 2008 server. Everything moved over fine, however now we are finding that we are getting collation conflicts. The old server had a different collation with the new server.

Now our tables created before the move are one collation, and the ones created after are another collation.

Is there a way to update the tables/columns with the old collation to the new collation?

I understand setting the default database/server collation does not modify any existing tables (link). I really don't want to recreate the database if I don't have to.

Any help really appreciated.

UPDATE

Thanks for your help guys, finally got it working.

For future reference, here is my final script:

SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name + 
    CASE systypes.NAME
    WHEN 'text' THEN ' '
    ELSE
    '(' + RTRIM(CASE SYSCOLUMNS.length
    WHEN -1 THEN 'MAX'
    ELSE CONVERT(CHAR,SYSCOLUMNS.length)
    END) + ') ' 
    END

    + ' ' + ' COLLATE Latin1_General_CI_AS ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
    FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
    WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
    AND SYSOBJECTS.TYPE = 'U'
    AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
    AND SYSCOLUMNS.COLLATION IS NOT NULL
    AND NOT ( sysobjects.NAME LIKE 'sys%' )
    AND NOT ( SYSTYPES.name LIKE 'sys%' )
    GO

Here is the site that contained the script I based it on. I had to tweak it to get it working correctly.

Proclivity

Russell, you should make your solution an answer! – Kirstiekirstin

Thanks Philipp, it is at the top of the question anyway, and OMG Ponies helped me get there. :) – Proclivity

Answer 1

You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.

Reference: Setting and Changing the Database Collation

If there are too many columns, you can loop through SYS.COLUMNS to apply the ALTER TABLE statement.

Conakry

Yeah that isn't my problem, my problem is with existing columns. – Proclivity

Which is why the last line says to use ALTER TABLE – Conakry

That's not an option as there are too many fields to do this to. – Proclivity

Per Microsoft documentation, that is the only option unless you recreate the tables. You could however loop through the columns via SYS.COLUMNS, running the ALTER TABLE statement. Would that be acceptable? – Conakry

lol, you rewrote your entire answer step by step in the comments. – Inglis

@Inglis sometime ponies lead horses to water? ;) – Crossover

Answer 2

Just in case anyone looking at this is using SQL server 2008, i had to make a couple modifications:

SELECT 'ALTER TABLE [' + sys.objects.name + '] ALTER COLUMN ['
+ sys.columns.name + '] ' + sys.types.name + 
    CASE sys.types.name
    WHEN 'text' THEN ' '
    ELSE
    '(' + RTRIM(CASE sys.columns.max_length
    WHEN -1 THEN 'MAX'
    ELSE CONVERT(CHAR,sys.columns.max_length)
    END) + ') ' 
    END

    + ' ' + ' COLLATE Latin1_General_BIN ' + CASE sys.columns.is_nullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
    FROM sys.columns , sys.objects , sys.types
    WHERE sys.columns.object_id = sys.objects.object_id
    AND sys.objects.TYPE = 'U'
    AND sys.types.system_type_id = sys.columns.system_type_id
    AND sys.columns.collation_name IS NOT NULL
    AND NOT ( sys.objects.NAME LIKE 'sys%' )
    AND NOT ( sys.types.name LIKE 'sys%' )

Exceptionable

Thanks for the tip. What was changed? I used the above script on SQL Server 2008. :D – Proclivity

This uses sys.columns rather than SYSCOLUMNS – Everybody

then how would you loop through this and execute the statements? – Dispermous

Answer 3

How about:

DECLARE @collation NVARCHAR(64)
SET @collation = 'Latin1_General_CI_AS'

SELECT
    'ALTER TABLE [' + TABLE_SCHEMA  + '].[' + TABLE_NAME + '] '
  + 'ALTER COLUMN [' + COLUMN_NAME + '] '
  + DATA_TYPE + '(' + CASE CHARACTER_MAXIMUM_LENGTH 
        WHEN -1 THEN 'MAX' 
        ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ') '
  + 'COLLATE ' + @collation + ' '
  + CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
FROM INFORMATION_SCHEMA.columns
WHERE  COLLATION_NAME IS NOT NULL
AND COLLATION_NAME <> @collation

Gosport

AND TABLE_NAME IN ( SELECT TABLE_NAME from information_schema.tables WHERE table_type = 'BASE TABLE' ) – Delacruz

Answer 4

To fix this problem, you need far more fire power then this script provides. I tried the script and ran into problems with dependent objects that couldn't update: indexes, keys and procedures. The final solution took just 5 minutes with this code project app. The app says it is for Sql Server 2000 but I used it successfully with 2008.

http://www.codeproject.com/Articles/12753/SQL-Server-2000-Collation-Changer

I can't stress this enough. BACKUP YOUR DATABASE. I had to use my backup three times to complete this task.

Argilliferous

Answer 5

One option is to use a program like Red Gate SQL Compare (I'm sure there's others too). With it you can generate script files for your schema with collation included (make sure to turn that on in the options), then do a search/replace in the files updating it to the new collation, then re-compare them back to your actual database.

At this point SQL Compare will be able to apply those changes (or save the changes to a script file if you prefer), and your existing columns are all fixed.

In theory you could do all of this while still under the trial period, though I would suggest it's a good tool to keep around as it makes many SQL tasks easier!

Joung

Redgate Compare is a great tool to use too. :D – Proclivity

Answer 6

Further to Owais Iqbal's answer rewritten the code by edosoft and put it in a loop to execute the actual T-SQL statements, you can grab the default collation from the database

-- **************** BEGIN INPUT **********************
USE [YourDBName]
-- **************** END INPUT ************************

-- **************** BEGIN GET DB COLLATION ***********
DECLARE @collation NVARCHAR(128)
SELECT @collation = collation_name
FROM sys.databases WHERE database_id = DB_ID()

PRINT 'Default database collation: ' + @collation
PRINT ''
-- **************** END GET DB COLLATION *************

-- **************** BEGIN LOGIC **********************

Alvinalvina

Answer 7

Okay I have rewritten the code by edosoft and put it in a loop to execute the actual T-SQL statements.

-- **************** BEGIN INPUT **********************
USE [YourDBName]

DECLARE @collation NVARCHAR(128)
-- enter you collation name below
SET @collation = N'Latin1_General_CI_AS'
-- **************** END INPUT ************************

-- **************** BEGIN LOGIC **********************
DECLARE @sqlCode VARCHAR(2048)

DECLARE myCursor CURSOR LOCAL FOR
    SELECT 'ALTER TABLE [' + sys.objects.name + '] 
        ALTER COLUMN ['+ sys.columns.name + '] ' + sys.types.name + 
        CASE sys.types.name
            WHEN 'text' THEN ' '
            WHEN 'ntext' THEN ' '
            ELSE '(' + RTRIM(
                CASE
                    WHEN sys.columns.max_length = -1 THEN 'MAX'
                    WHEN sys.columns.max_length > 4000 THEN 'MAX'
                    ELSE CONVERT(CHAR,sys.columns.max_length)
                END) + ')' 
        END
        + ' COLLATE ' + @collation + CASE sys.columns.is_nullable WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END
        FROM sys.columns , sys.objects , sys.types
        WHERE sys.columns.object_id = sys.objects.object_id
            AND sys.objects.TYPE = 'U'
            AND sys.types.system_type_id = sys.columns.system_type_id
            AND sys.columns.collation_name IS NOT NULL
            AND sys.columns.collation_name <> @collation
            AND NOT ( sys.objects.NAME LIKE 'sys%' )
            AND NOT ( sys.types.name LIKE 'sys%' )

OPEN myCursor
FETCH NEXT FROM myCursor INTO @sqlCode

WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'Executing: ' + @sqlCode
    BEGIN TRY
        EXEC(@sqlCode);
        PRINT 'Done!' + CHAR(10)
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE() + CHAR(10)
    END CATCH
    FETCH NEXT FROM myCursor INTO @sqlCode
END

PRINT 'Finished!'
-- **************** END LOGIC **********************

If you get the error something similar to "Cannot create a row of size 8075 which is greater than the allowable maximum row size of 8060". Rebuild the table for which you are getting the error and run the above script again.

ALTER TABLE [dbo].[YourTableName] REBUILD 

Kelda

Answer 8

The code fails to take NText, NChar and NVarchar double bytes into account. If you have NText it will fail with Ntext(16) unable to set size on NText.

For NChar and NVarChar it doubles the length, because it fails to divide size by 2.

Another Quirky little detail, is that for Nvarchar at least, -1 is not MAX, but 0 is.

This is a very very ugly hack on the code, just to illustrate the issues:

ALTER TABLE [BlanketBruger] ALTER COLUMN [BrugerNavn] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BlanketBruger] ALTER COLUMN [BrugerFuldNavn] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [blanketgruppe] ALTER COLUMN [GruppeNavn] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [blanketSerie] ALTER COLUMN [SerieTitel] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [blanketSerie] ALTER COLUMN [SerieAlias] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [FormUse] ALTER COLUMN [HostName] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BackendLog] ALTER COLUMN [value1] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [value2] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [ip] varchar(20)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BackendLog] ALTER COLUMN [username] nvarchar(100)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [gruppenavn] nvarchar(100)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [scriptname] nvarchar(100)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BackendLog] ALTER COLUMN [querystring] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [useragent] nvarchar(400)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [sessionid] varchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [htmlcontent] nvarchar(MAX)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [value1] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [value2] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [ip] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [querystring] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [useragent] nvarchar(400)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [sessionid] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [log4net] ALTER COLUMN [Thread] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [log4net] ALTER COLUMN [Level] varchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [log4net] ALTER COLUMN [Logger] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [log4net] ALTER COLUMN [Message] text   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [log4net] ALTER COLUMN [Exception] varchar(MAX)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [log4net] ALTER COLUMN [Server] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [OioSamlLog] ALTER COLUMN [Server] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [OioSamlLog] ALTER COLUMN [Thread] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [OioSamlLog] ALTER COLUMN [Level] varchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [OioSamlLog] ALTER COLUMN [Message] varchar(MAX)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BlanketSubmitTemp] ALTER COLUMN [FileContentIdentifier] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BlanketSubmitTemp] ALTER COLUMN [FileContent] ntext   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BlanketSubmitTemp] ALTER COLUMN [FileName] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Dickdicken

the actual query code would be more helpful than example output; we can't tell that any of those nvarchar columns are double size.. – Underpinnings