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.


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



  5. Igor Kovalenko says:


    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


    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.


  12. Igor Kovalenko says:


    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.


  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.



  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:


    Also — I had to add a few lines:



    , cc.is_not_for_replication


    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!


  16. Bruno says:

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

  17. Evgenij says:


    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.


    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.



  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.


  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.


  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


  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


    I think this is because :-

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

    — drop check constraints

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

    PRINT ‘– ***’


    IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb.dbo.[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


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

    PRINT @stmt

    IF $(script_only) = 0


    IF @@error <> 0






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


    — close cursor

    CLOSE ms_cc_cursor

    DEALLOCATE ms_cc_cursor



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

    — 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:


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



  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.



  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:

    <p>PRINT &#39;-- ***&#39;</p>
    <p>PRINT &#39;-- COLUMNS COLLATION IN PROGRESS...&#39;</p>
    <p>IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb.dbo.char_cols)</p>
    <p>DECLARE ms_col_cursor CURSOR FOR</p>
    <p>select owner, table_name, [col_name], length, type_name, nullable, is_user_defined</p>
    <p>from tempdb.dbo.char_cols</p>
    <p>open ms_col_cursor</p>
    <p>fetch next from ms_col_cursor into @owner, @table_name, @col_name, @length, @type_name, @nullable, @flag</p>
    <p>WHILE @@fetch_status &gt;= 0</p>
    <p>IF @type_name=&#39;varchar&#39; AND @length=-1</p>
    <p>		SET @type_name=&#39;text&#39;</p>
    <p>		SET @length=0</p>
    <p>IF @type_name=&#39;varchar&#39; AND @length=0</p>
    <p>		SET @type_name=&#39;nvarchar&#39;</p>
    <p>		SET @length=&#39;MAX&#39;</p>
    <p>	SET @stmt = &#39;ALTER TABLE [&#39; + @owner + &#39;].[&#39; + @table_name + &#39;] ALTER COLUMN [&#39; + @col_name + &#39;] &#39; </p>
    <p>		+ @type_name </p>
    <p>	-- length</p>
    <p>	IF NOT (UPPER(@type_name COLLATE DATABASE_DEFAULT) LIKE &#39;%TEXT&#39; OR </p>
    <p>			UPPER(@type_name COLLATE DATABASE_DEFAULT) = &#39;SYSNAME&#39; OR </p>
    <p>			@flag = 1)</p>
    <p>		SET @stmt = @stmt + &#39;(&#39; + cast(@length as varchar(4)) + &#39;)&#39; </p>
    <p>	IF @flag = 0</p>
    <p>		SET @stmt = @stmt + &#39; COLLATE DATABASE_DEFAULT &#39; </p>
    <p>	SET @stmt = @stmt + &#39; &#39; + @nullable</p>
    <p>	PRINT @stmt</p>
    <p>	IF $(script_only) = 0</p>
    <p>		EXEC(@stmt)</p>
    <p>	IF @@error &lt;&gt; 0 </p>
    <p>	BEGIN</p>
    <p>		RETURN</p>
    <p>	END</p>
    <p>	fetch next from ms_col_cursor into @owner, @table_name, @col_name, @length, @type_name, @nullable, @flag</p>
    <p>-- close cursor</p>

Skip to main content