ALTER DATABASE … ALTER COLLATION (FORCED).


As all of you know Microsoft has made a small problem for all SQL DBA and developers. If you have a database with any of SQL_xxxxx collations (for more details please have a look at the TERTIARY_WEIGHTS() function here), you will defenetily have a significant problem with index search and ordering performance if you’ll try to upgrade your SQL Server with 2005 version. Some of developers was excited when first time reading this article, really :-).


Suddenly ALTER DATABASE .. ALTER COLLATION command will change database collation for you, but all objects will stay “as is” with old collation (by design). The only way to change collation for all existing objects is to create a new empty database with new desired collation and copy data into this new database (copy wizzard, for example).


To sort this issue out i created this small script (in attachment). It was tested both manually and with using VSTS DBPro, and i can confirm that you can change the collation of your database without any problem and manual work. As an input you should provide it with database with one collation, and as output you will have the same database with another (desired) collation. So as for me this is an analoque of ALTER DATABASE … ALTER COLLATION FORCED command :-). How it works:


– you should use sqlcmd or SSMS in sqlcmd mode,


– specify database name as a parameter,


– specify desired collation as a parameter,


– run it and wait for results.


What is not covered by the script: table and index partitioning. I guess that if you are using these advanced options, you are smart enough to change this script to add required feature. Mostly i created this script for the following scenario: (1) detach database from SQL Server 6.5/7.0/200 version; (2) attach it to SQL Server 2005; (3) run this script; (4) use your database asap.


Some words about performance: than more tables with computed columns (computed column not at the end of the table) you have than more time you will have to wait. 100 Gigabyte db can be easily transformed within one hour on AI64 4way with EVA8000.


Also it is possible to use this script to study SQL 2005 system views and relationship between them.


Many thanks for any reply and comments.


 —- January 2008


Many thanks to WaitForPete, script updated to fix these (and some other) issues.

change_collation.sql

Comments (48)

  1. JohnGalt says:

    Will this allow me to go through and reset the collation on every single field in the database to the database default??? It’s killing me what SQL Server does changing fields randomly all of the time!

  2. Igor Kovalenko says:

    Sure, JohnGalt. This script will go through every field in database and will change field collation. Even if this field is a member of primary constraint, has referensed by caclulated field, indexed, referensed by foreign key constraint.. whatever you want :-)

  3. Rod Weir says:

    Hi Igor,

    Thanks for a great script.  What a timesaver!

    The script seems to have a problem scripting VarChar(MAX) columns.  The script ends up looking like this…

    ALTER TABLE [dbo].[tblJobFinderSearch] ALTER COLUMN [ClientLink] varchar(-1) COLLATE DATABASE_DEFAULT  NULL

    Notice the (-1) for the varchar setting?  This column used to be VarChar(Max).

    Any ideas?

    Thanks Rod.

  4. Rod Weir says:

    Here’s some other things to watch out for….

    — Make a backup of the database first!!!  If this script fails, it WILL leave the database in a half-repaired state where it may be missing all of the indexes, primary keys, constraints and other objects

    — Change any VarChar(MAX) columns back to VarChar(n) and then change back to MAX after running.

    — Remove any Full Text Catalogs before running this script

    — Run DBCC CHECKDB to identify any database errors or inconsistencies

    — Run DBCC UPDATEUSAGE(0) on database first, especially if the database has been upgraded from SQL Server 2000 to 2005

    Thanks,

    Rod

  5. Igor Kovalenko says:

    Rod,

    thanks a lot for your comment. We’ve identified issue with SQL Server collations during consolidation server process + migration from SQL 2000 to 2005 version. As far as SQL 2000 does not implement char(max) datatype these (any of max) datatypes are not covered by the script. You are absolutely right with Full Text Catalog feature – it might be usefull approach to drop and recreate all catalogues. Please also pay attention on partitioned tables and indexes – partitioning ans partitioned fuctions are also doesn’t covered by the script. Hopefully you can use this script as a template to add any additional functions as you wish.

  6. Do you have the same script but for SQL 2000

  7. Igor Kovalenko says:

    No. It’s to complicated for me because of lack of (n)varchar(max) data type. But you can try to look for AlterCollation.exe program (i saw this freeware tool with sources for SQL 2000). During testing i found a few bugs in it, but it is better then nothing.

  8. John Parker says:

    Thanks a lot , saved me a fair bit of time.

    Thought you should know there is a bug in the script if you are running it on a database which is already case sensitive from SSMS

    The statement DECLARE cviews should be changed to DECLARE cViews

  9. Igor Kovalenko says:

    John, thank you. I’ll try to improve the script next time when i meet with case sensitive.

  10. Jaume Simon says:

    Thank you, I downloaded your script and it ran flawlessly, everything seems to be ok and collated the same way.

  11. WaitForPete says:

    Hi Igor

    this script is most useful for our SQL 2005 customers who have fallen foul of this trap, but there is NO sign of the AlterCollation.exe program that you mentioned for SQL 2000.

    I am having a look at porting the script back to 2000 but am floundering with the new system tables that you are using.

    e.g. this clause crops up repeatedly

    FROM sys.sql_modules m

    JOIN sysobjects o on o.[objectid] = m.[objectid]

    join sys.schemas s on s.schema_id = o.schema_id

    WHERE (

    m.uses_database_collation = 1

    or

    m.is_schema_bound = 1

    )

    but I haven’t yet found a SQL 2000 equivalent for the uses_database_collation or is_schema_bound columns.

    Obviously it would be far more efficient to locate the program, but right now the only search result that comes up is this one!  I did also look in the SQL 2000 media but it looks like that is not what you meant.

    Any pointers most gratefully received.

    Pete

  12. Igor Kovalenko says:

    Pete,

    Suddenly i don’t have SQL 2000 nearby me, mostly SQL 2008 :-). I may firget something, but i think you can’t find the analoque in SQL 2000. As for me it will be much easier to drop/recreate ALL stored procedures / functions / views instead of analysing column set. This will (1) simplify script (2) speedup processing.

    Igor

  13. WaitForPete says:

    I am allowed to drop support forSQL 2000 soon, in the meantime….

    We have the drop and recreate script for all the programability objects anyway so it looks like sharp scissors time for that section.

    Thanks

    Pete

  14. WaitForPete says:

    PS, I did find one bug on the way through.  The lines

    IF @delete_referential_action = 1

    SET @stmt = @stmt + ‘ NOT FOR REPLICATION’

    , appearing in a couple of locations, are incorrect, they should be

    IF @is_not_for_replication = 1

    SET @stmt = @stmt + ‘ NOT FOR REPLICATION’

  15. David Punnett says:

    GREAT SCRIPT!

    Also — I had to add a few lines:

    Alter the order of the FK Constraints from: ON UPDATE CASCADE NOT FOR REPLICATION ON DELETE CASCADE  to   ON UPDATE CASCADE ON DELETE CASCADE NOT FOR REPLICATION  to avoid syntax problems.

    Added:

    , cc.is_not_for_replication

    to

    SELECT cc.[name]

    , par_obj_name = o.[name]

    , owner = s.[name]

    , cc.definition

    , [with_check] = case cc.is_not_trusted when 1 then ‘WITH NOCHECK’ else ” end

    , cc.is_not_for_replication

    FROM sys.check_constraints cc

    join sys.objects o on o.[object_id] = cc.parent_object_id

    join sys.schemas s on s.schema_id = o.schema_id

    to get "NOT FOR REPLICATION" into the check constraints

    Added:     SET @obj_def = CASE WHEN left(@obj_def, 1) = ‘(‘ THEN right(@obj_def, len(@obj_def) – 1) END

    SET @obj_def = CASE WHEN right(@obj_def, 1) = ‘)’ THEN left(@obj_def, len(@obj_def) – 1) END

    to remove the double parenthesis that the default constraints get after the script.

    Otherwise – GREAT SCRIPT!  

    Thanks much!

    David

  16. Bruno says:

    Thank you for sharing your work Igor. This was a great timesaver!

  17. Evgenij says:

    Hi,

    the script does not work for me converting Lithuanian_CI_AS to Latin1_General_CI_AI

    All Lithuanian letters look incorrect

    Great pity.

    Glad it worked for others.

    Thanks for sharing, anyway.

  18. bnornes says:

    I’ve tested the script on my CRM 3.0 datbase (backup). But the script fails on

    ALTER TABLE [dbo].[fn_CollectForCascadeAssign] DROP CONSTRAINT [DF__fn_Collec__proce__5A8539BC]

    Msg 4902, Level 16, State 1, Server PCSTEST1, Line 1

    Cannot find the object "dbo.fn_CollectForCascadeAssign" because it does not exist or you do not have permissions.

    — DROP DEFAULT CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.

    Msg 50000, Level 16, State 1, Server PCSTEST1, Line 1245

    It seems that the "Table-valued Functions" is deleted somewhere in the script.

    If anyone could fix this problem I would be very thankful.

    regards

    bjorn

  19. Mark Dell says:

    I have found that you can easily modify this script to support database names with strange characters in the name by replacing all of the instances of "$(destdb)" with "[$(destdb)]" (excluding the quotes of course) except for the use in the following query.  It should remain as shown here.

    SELECT @RecoveryModel = recovery_model

    FROM sys.databases

    WHERE [name] = ‘$(destdb)’

  20. MDostie says:

    Thanks a lot Igor.  Your really well done script saved my butt.

    Only issue I had was a user defined fonction using a varchar(8000), changed it before running the script and all was fine.

    With a database compare I also noticed an index drop a desc clause on a date field.

  21. Mjim says:

    I see back in March 2007 Steeve Gauvreau asked if there was a SQL 2000 equivalent script.  Has anyone found such a script yet?

  22. kohmars says:

    OMG – You rock! This was awesome, not so painful and complicated as I thought! I am no beginner with Sql Server but I am with 2005, so, I had some issues figuring out the sqlcmd, so incase anyone else needs a sample:

    from dos prompt

    sqlcmd -S your_server_name -U sa -P your_sa_password -i "change_collation.sql" -o "change_collation.out"

  23. Elias Suleiman says:

    That is a great script. Thank you!

  24. matro says:

    this saved me a lot of hours, really thank you for your work.

    worked flawlessly on SQL Server 2005 SP2 64-Bit.

  25. Elias says:

    Really nie.

    Worked fine, except I got errors in the very last section of the script.

    Msg 16916, Level 16, State 1, Line 3247

    A cursor with the name ‘cViews’ does not exist.

    Msg 16916, Level 16, State 1, Line 3253

    A cursor with the name ‘cViews’ does not exist.

    Msg 16916, Level 16, State 1, Line 3276

    A cursor with the name ‘cViews’ does not exist.

    Msg 16916, Level 16, State 1, Line 3277

    A cursor with the name ‘cViews’ does not exist.

  26. Jie says:

    This script worked great for me on SQL 2005 server and fixed the issue that I plan the big effort on othe way.

    I really appreciate your work.

  27. PC says:

    Great work. It is people like you that keep people like me in a job.

    Cheers

  28. MC says:

    Works flawlessly on my databases that have hundreds of columns with all sorts of constraints, etc. Thank you very much for saving me the trouble and time. Really appreciate your sharing this.

  29. JMG says:

    Many thanks your script worked a treat on a database I’m migrating.

  30. Marco Alves says:

    Terrific job. Congratulations.

    Thx.

  31. Pablo Doval says:

    Excelent script!! Thanks for sharing!

  32. Edgardo Moguella says:

    Excelent script!!!!!

    I really appreciate your sharing this.

  33. Steve Jones says:

    Hi –

    Great script!! Couldn’t face scripting out all the dependant objects.  

    Loving the script only option too.  Just one change I’ve made:

    In the "start changing collation" section, so that collation isn’t changed if you’re only interested in the scripting:

    IF $(script_only) = 0 ALTER DATABASE $(destdb) COLLATE $(desired_collation)

    Thanks again

    Steve

  34. Steve Jones says:

    Sorry one more changes – you want to drop the tempdb tables when you’re scripting also so have commented out the "IF $(script_only) = 0" in the "prepare temporary tables" section.

    Again, many thanks!!

  35. Ricky Stretch says:

    I have worked successfully with this script before.  I am running it on another DB and I get this error:

    Msg 207, Level 16, State 3, Line 2

    Invalid column name ‘object_id’.

    Has anyone experienced this and if so what might the resolution be.

  36. Mark B says:

    In the

    — drop check constraints

    section, I don’t think the code is right when it completes the dropping, as it always reports

    PRINT ‘– DROP CHECK CONSTRAINTS failed’

    I think this is because :-

    — ****************************************************************

    — drop check constraints

    — ****************************************************************

    PRINT ‘– ***’

    PRINT ‘– DROP CHECK CONSTRAINTS’

    IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb.dbo.[check_constr])

    GOTO SKIP_DROP_CHECK_CONSTR

    DECLARE ms_cc_cursor CURSOR FOR

    SELECT [name], [owner], par_obj_name FROM tempdb.dbo.[check_constr]

    open ms_cc_cursor

    fetch next from ms_cc_cursor into @obj_name, @owner, @table_name

    WHILE @@fetch_status >= 0

    BEGIN

    SET @stmt = ‘ALTER TABLE [‘ + @owner + ‘].[‘ + @table_name + ‘] DROP CONSTRAINT [‘ + @obj_name + ‘]’

    PRINT @stmt

    IF $(script_only) = 0

    EXEC(@stmt)

    IF @@error <> 0

    BEGIN

    PRINT ‘– DROP CHECK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.’

    RAISERROR(‘DROP CHECK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.’, 16, 1)

    RETURN

    END

    fetch next from ms_cc_cursor into @obj_name, @owner, @table_name

    END

    — close cursor

    CLOSE ms_cc_cursor

    DEALLOCATE ms_cc_cursor

    SKIP_DROP_CHECK_CONSTR:

    PRINT ‘– DROP CHECK CONSTRAINTS failed’

    — ****************************************************************

    — drop indexes

    — ****************************************

    After the DEALLOCATE ms_cc_cursor you don’t jump the SKIP_DROP_CHECK_CONSTR: element and therefore always print a failure.

    Also, is there any chance a summary could be printed at the bottom of the log??

  37. Stefaan Margot says:

    hi,

    I was wondering if this script also works on a SQL Server 2008?

    TIA

    Stefaan.

  38. NiceLady says:

    Great script! Excellent work. Many thanks.

  39. Jonathan Lee says:

    Very Cool!!! Definitely saved my butt migrating from our legacy (aka SQL 6.5) Great Plains Dynamics server collation to SQL 2005.

    Many thanks.

  40. Eric Jonk says:

    Do not try to run the script on multiple databases at the same time.

    The use of "tempdb.dbo." will create the temporary tables in the system database tempdb.

    When working on two databases at the same time the table will already be created and all things go wrong from that point.

    Replacing "temp.dbo." with a # will create a real temporary table for each opened query window.

    Without dropping the temporary tables however, the script will not run in the same query window after editing the databasename to work on (destdb).

  41. Wait For Pete says:

    The published script does not support the varcharnvarchar(max) data types.  Fortunately it seems this is easily remedied by locating the line

    SET @stmt = @stmt + '(' + cast(@length as varchar(4)) + ')'

    at about line number 1830

    and replacing it with

    SET @stmt = @stmt + '(' + case when @length = 0 then 'max' else cast(@length as varchar(4)) end + ')'

    which I confess is a hack.

    There are a couple of other instances of this cast which you may find it necessary to change,

    search for cast(@length)

    however I cannot vouch for them as they are not necessary in our case.

    Cannot recommend too highly to drop all Views, SPs and functions beforehand and recreate them afterwards.

  42. Nicola Tuveri says:

    Very useful! Thanks.

    Just to let you know, I have made some little changes:

    – replaced SET @stmt = @stmt + '(' + cast(@length as varchar(4)) + ')'

    with SET @stmt = @stmt + '(' + case when @length <= 0 then 'max' else cast(@length as varchar(4)) end + ')'

    as suggested by WaitForPete (note the <= instead of just =)

    – dropped manually unique filtered index, as they are not supported by the script

  43. Landon Wright says:

    Hey Igor,

    Will this script work for a SQL 2008 db that is being moved to SQL 2012?

    I'll admit that I have only scanned the script at this time. I plan to dig into it soon, but if you or someone else knows the answer to this or can give me some direction that would be great.

    Thanks,

    Landon

  44. David Samson says:

    Igor, this script is fantastic, thank-you.  It worked well for me when moving from 2008 to 2008 R2.

    After several failures that left the database in an unusable state, I decided I'd rather have the script do everything it can and then clean up the errors myself, afterwards.  To do this, I commented out the RAISERROR and RETURNs after failures (there were 71 total).

    I anticipated having to fix the root cause of the issues that came up and then rerun the script, but I was lucky; all of the errors I fixed after the fact by simply tweaking and rerunning the SQL.

    Thanks again Igor.

  45. Saurabh Agrawal says:

    Thanks a lot .. Anybody can use it . I approve …

  46. Joy George says:

    change_collation.sql, this script worked even for SQL Server 2012.

    My requirement was to convert db collation Russian and it worked.

    The script is 8 years old and still going.

    Great Job

    Thank you

  47. Luis Henriques says:

    This is the best script to convert collation.

    The problem of VarChar(MAX) columns, i fix it with:

    [length] [varchar](4) NULL,

    , length = case when cast(c.max_length as varchar(4)) like  '-1' then 'MAX' else cast(c.max_length as varchar(4)) end

    Thanks a LOT

  48. Alex says:

    Actually there are plenty of things in this script that should be fixed.

    when column type is varchar and size is -1 that means that the column is Text. Also when type is varchar and size 0, that means that the column type is nvarchar and size max.

    Follows update:

    [code]

    PRINT '– ***'

    PRINT '– COLUMNS COLLATION IN PROGRESS…'

    IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb.dbo.char_cols)

    GOTO SKIP_DROP_CHAR_COLS

    DECLARE ms_col_cursor CURSOR FOR

    select owner, table_name, [col_name], length, type_name, nullable, is_user_defined

    from tempdb.dbo.char_cols

    open ms_col_cursor

    fetch next from ms_col_cursor into @owner, @table_name, @col_name, @length, @type_name, @nullable, @flag

    WHILE @@fetch_status >= 0

    BEGIN

    IF @type_name='varchar' AND @length=-1

    BEGIN

    SET @type_name='text'

    SET @length=0

    END

    IF @type_name='varchar' AND @length=0

    BEGIN

    SET @type_name='nvarchar'

    SET @length='MAX'

    END

    SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ALTER COLUMN [' + @col_name + '] '

    + @type_name

    — length

    IF NOT (UPPER(@type_name COLLATE DATABASE_DEFAULT) LIKE '%TEXT' OR

    UPPER(@type_name COLLATE DATABASE_DEFAULT) = 'SYSNAME' OR

    @flag = 1)

    SET @stmt = @stmt + '(' + cast(@length as varchar(4)) + ')'

    IF @flag = 0

    SET @stmt = @stmt + ' COLLATE DATABASE_DEFAULT '

    SET @stmt = @stmt + ' ' + @nullable

    PRINT @stmt

    IF $(script_only) = 0

    EXEC(@stmt)

    IF @@error <> 0

    BEGIN

    PRINT '– ALTER COLUMN FAILED. SEE ERROR LOG FOR DETAILS.'

    RAISERROR('ALTER COLUMN FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)

    RETURN

    END

    fetch next from ms_col_cursor into @owner, @table_name, @col_name, @length, @type_name, @nullable, @flag

    END

    — close cursor

    [/code]