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.

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.

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.

Answer 2

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!

Answer 3

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%' )