Click here to Skip to main content
11,802,636 members (67,879 online)
Click here to Skip to main content

SQL Server 2000 Collation Changer

, 3 Mar 2008 CPOL 423.7K 7.7K 99
Rate this:
Please Sign up or sign in to vote.
Change collation order for all text columns in a database


Do you have an SQL application that you need to deploy in another country? Do you need to change the collation of your SQL database and all objects in it? If the answer is yes, then this could be a very slow process to do manually. If you run the alter database script as below:

ALTER DATABASE [My_Database] COLLATE My_Collation

You will find that the database default collation is changed for new columns, but all existing columns will retain the original collation order. Changing the collation on each column is a non-trivial task, as you need to drop all indexes, full text indexes and constraints associated with the column, along with any user-defined functions. Once the collation order has been changed, you can recreate the indexes, constraints and functions. This C# tool simplifies the process by creating an SQL script that does everything for you.

Using the Code

Simply run the program, select your SQL Server database and a new collation order. You have two options: you can either simply get the program to create an SQL script that you can run later (press the "Script Only" button) or you can actually make the changes (press the "Script and Execute" button). Things to note:

  • Always back up your database before running this tool. I cannot guarantee that you will not lose data. A number of the statements cannot be run in a transaction, so there is no way of detecting a failure and rolling back.
  • On SQL Server 2000, nText columns will be recreated so your column order will be slightly different.
  • To run the script, the program sets the database into single user mode to run the ALTER DATABASE [db_name] COLLATE [Collation Name] statement. You should therefore ensure that there are no open connections on the database before running the script (use the stored procedure SP_WHO to identify any open connections).
  • Since the script will drop and then rebuild all indexes and foreign keys in the database, you will find that it could take a long time to complete (possibly hours).
  • All columns will be changed to the new collation order, even if they have a non-default collation before running the script.
  • SQL 2005 support is gradually being added. If you encounter issues or missing functionality, please let me know.
  • If you change from a case-insensitive to a case-sensitive collation order, you may find errors occurring when recreating check constraints and functions. This is because your scripts will be parsed in a case-sensitive manner once the collation order has been changed. To work around this, I would recommend running the script in the program and reviewing the output once the script has run to completion. The error messages relating to each failure will be displayed in red under the code that failed.

Change History

18 January 2006

  • Original posting.

7 March 2006

  • Fixed bug where scripting failed on databases with case-sensitive collation orders. Script no longer drops foreign key constraints unless necessary.

30 August 2006

  • Fixed bug when scripting objects owned by different owners than the owner executing the script are used. May be seen as an error when scripting to #spindtab_____.
  • Fixed bug where scripting did not recreate permissions on table functions after they were recreated.

20 March 2007

  • Triggers now disabled while changes are made.
  • Altered sequence of execution to prevent errors following user feedback.
  • Added some SQL 2005 support. Let me know if you encounter any issues.
  • Resolved issue when recreating a table function where the body of a table function is greater than 4000 characters.
  • Reinstated the functionality to only delete the required indexes and primary keys.

10 October 2007

  • Added support for changing collation where full text indexes exist, including changing language used in the full text search.
  • Fixed issue when insteadof triggers exist on views.
  • Fixed issue where nText columns end up allowing nulls when they were originally declared NOT NULL.
  • SQL 2005 allows collation order of nText columns to be modified. The script has been modified to reflect this new functionality.
  • Fixed issues encountered when user-defined data types are used.
  • Added additional SQL 2005 support, including refactoring scripting logic to allow various parts of the script to be customised for different versions of SQL server. If you need to debug or customise the scripts, it should be a little easier to understand.
  • Converted from VB.NET to C#, as I use C# at work all the time and it is more familiar to me.
  • Moved execution task to a worker thread to give a more responsive UI.
  • Fixed issues when system databases have case-sensitive sort order.

1 March 2008

  • Created new code for handling indexes, statistics and relationships in SQL 2005. This new code uses the 2005 schema views and adds scripting support for new SQL 2005 functionality, such as included columns in non-clustered indexes.
  • Fixed issues where ANSI_NULLS setting is not correct after recreation of table functions.
  • Various minor UI bugs fixed.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Alex Baker
Software Developer RXP Services
Australia Australia
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
Thirks30-Jun-13 23:17
memberThirks30-Jun-13 23:17 
SuggestionExcellent Program (small update) Pin
Rambauldi24-May-13 0:41
memberRambauldi24-May-13 0:41 
GeneralMy vote of 5 Pin
a1nt14-May-13 8:19
membera1nt14-May-13 8:19 
GeneralMy vote of 5 Pin
Elham M19-Jan-13 0:39
memberElham M19-Jan-13 0:39 
BugDoes not disable trigger and drop functions correctly Pin
TheSilence873-Dec-12 3:26
memberTheSilence873-Dec-12 3:26 
QuestionIt worked well with SQL 2008 R2 - Thank you very much!!! Pin
LJMOU927-Nov-12 10:24
memberLJMOU927-Nov-12 10:24 
QuestionSQL Server 2000 Collation Changer works fine with SQL2008R2 Pin
3lol12-Sep-12 3:39
member3lol12-Sep-12 3:39 
GeneralThanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Works fine in SQL2008R2 Pin
Member 90598361-Jun-12 5:28
memberMember 90598361-Jun-12 5:28 
QuestionDoes not include filter clause of filtered index Pin
Cor Westra29-May-12 3:08
memberCor Westra29-May-12 3:08 
QuestionWorks great on SQL 2008R2! Pin
rbvdberg7-May-12 1:36
memberrbvdberg7-May-12 1:36 
QuestionPerfect!It works fantastic with SqlServer 2008 Pin
Rohit Dubey from Hyderabad29-Feb-12 20:52
memberRohit Dubey from Hyderabad29-Feb-12 20:52 
Questioncool app thanks Pin
Member 300160524-Feb-12 19:36
memberMember 300160524-Feb-12 19:36 
GeneralGenius Pin
jeremykirkup24-Feb-12 2:51
memberjeremykirkup24-Feb-12 2:51 
QuestionTHANK YOU Pin
Member 420507923-Feb-12 0:23
memberMember 420507923-Feb-12 0:23 
GeneralOur vote of 5 and our thanks Pin
albertberenguer9-Nov-11 23:46
memberalbertberenguer9-Nov-11 23:46 
GeneralMy vote of 5 Pin
albertberenguer9-Nov-11 23:42
memberalbertberenguer9-Nov-11 23:42 
QuestionWorked like a charm on SQL 2008! Pin
Member 47573309-Nov-11 2:48
memberMember 47573309-Nov-11 2:48 
GeneralMy vote of 5 Pin
TobiasL14-Sep-11 4:47
memberTobiasL14-Sep-11 4:47 
GeneralMy vote of 5 Pin
Member 389417924-Aug-11 1:28
memberMember 389417924-Aug-11 1:28 
GeneralMy vote of 4 Pin
koms1629-Jun-11 13:46
memberkoms1629-Jun-11 13:46 
QuestionWorked flawlessly! Pin
Xechorizo28-Jun-11 5:25
memberXechorizo28-Jun-11 5:25 
GeneralMy vote of 5 Pin
blaxun9-Jun-11 4:21
memberblaxun9-Jun-11 4:21 
General3 thumbs up Pin
Member 791568211-May-11 10:12
memberMember 791568211-May-11 10:12 
GeneralMy vote of 5 Pin
KeithWatson21-Apr-11 1:50
memberKeithWatson21-Apr-11 1:50 
GeneralTHANK YOU!!! Pin
Member 785873420-Apr-11 4:54
memberMember 785873420-Apr-11 4:54 
GeneralMy vote of 5 Pin
ranimi24-Nov-10 5:51
memberranimi24-Nov-10 5:51 
GeneralMy vote of 5 Pin
Ziza6-Aug-10 0:26
memberZiza6-Aug-10 0:26 
GeneralError when trying run Collation Changer for a SQL 2005 environment Pin
vchokhani16-Apr-10 12:07
membervchokhani16-Apr-10 12:07 
GeneralCongratulations !!! Pin
Samuel Mendes22-Dec-09 8:58
memberSamuel Mendes22-Dec-09 8:58 
GeneralSource code not work on VS 2008 Pin
utehn11-Oct-09 18:16
memberutehn11-Oct-09 18:16 
GeneralYou're the best! Pin
SOEP15-Sep-09 6:59
memberSOEP15-Sep-09 6:59 
GeneralThanks !!! Pin
ismoonoy3-Sep-09 21:38
memberismoonoy3-Sep-09 21:38 
GeneralIssue: doesnt script Cross Apply in Joins Pin
Member 390210212-Aug-09 10:40
memberMember 390210212-Aug-09 10:40 
GeneralProblem scripting Schemas Pin
Member 39021026-Aug-09 9:07
memberMember 39021026-Aug-09 9:07 
GeneralThank You - A Most Excellent Piece of Software Pin
KeithWatson9-Jun-09 5:51
memberKeithWatson9-Jun-09 5:51 
QuestionWhy not updating syscolumns directly? Pin
aldg23-Apr-09 16:58
memberaldg23-Apr-09 16:58 
QuestionUpdate statistics? Pin
aldg23-Apr-09 16:49
memberaldg23-Apr-09 16:49 
GeneralAwesome Pin
micheal_safian19-Feb-09 2:16
membermicheal_safian19-Feb-09 2:16 
GeneralFantastic! Pin
ii_noname_ii28-Jan-09 3:09
memberii_noname_ii28-Jan-09 3:09 
QuestionDrop Indexes?? Pin
Bipkins15-Oct-08 22:44
memberBipkins15-Oct-08 22:44 
GeneralThis is briliant... Pin
joeharris769-Oct-08 9:40
memberjoeharris769-Oct-08 9:40 
QuestionHaving 137 - Must declare the scalar variable "@C". error? Pin
Member 267843129-Sep-08 9:35
memberMember 267843129-Sep-08 9:35 
AnswerRe: Having 137 - Must declare the scalar variable "@C". error? Pin
websteri16-Jun-09 15:01
memberwebsteri16-Jun-09 15:01 
GeneralRe: Having 137 - Must declare the scalar variable "@C". error? Pin
Alex Baker16-Jun-09 15:03
memberAlex Baker16-Jun-09 15:03 
GeneralRe: Having 137 - Must declare the scalar variable "@C". error? Pin
websteri16-Jun-09 15:08
memberwebsteri16-Jun-09 15:08 
GeneralRe: Having 137 - Must declare the scalar variable "@C". error? Pin
Alex Baker16-Jun-09 15:10
memberAlex Baker16-Jun-09 15:10 
GeneralRe: Having 137 - Must declare the scalar variable "@C". error? Pin
websteri16-Jun-09 17:10
memberwebsteri16-Jun-09 17:10 
QuestionEncrypted functions and stored procedures? Pin
johny prentice20-May-08 15:53
memberjohny prentice20-May-08 15:53 
QuestionCan i use this to Remove the Collation? Pin
Anugrah Atreya6-May-08 21:58
memberAnugrah Atreya6-May-08 21:58 
GeneralCTRL^C CTRL^V bug :) Pin
adams@mdgmedical.com1-May-08 4:33
memberadams@mdgmedical.com1-May-08 4:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.151002.1 | Last Updated 3 Mar 2008
Article Copyright 2006 by Alex Baker
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid