SQL SERVER – Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script

This document is created by Brian Cidern, he has written this excellent extension to SQL Expert who SQL SERVER – Change Collation of Database Column – T-SQL Script. His scripts are not only extremely helpful to achieve the task of consolidating collations in quick script. His script not only works perfectly but excellent piece of code and logic.

Hats off to you Brian! You can reach Brian at his email address (brians.sql.blog (at) gmail (dot) com) or leave comment here.

Download all scripts and explanation here

About Collation Consolidation

At some time in your DBA career, you may find yourself in a position when you sit back and realize that your database collations have somehow run amuck, or are faced with the ever annoying CANNOT RESOLVE COLLATION message when trying to join data of varying collation settings.  I’m sure there are as many reasons as to how you’ve come to this juncture as there are readers.  Maybe you have DBA’s and instances around the world and the local staff are using their own collations.  Or, maybe you’ve learned the hard way that ALTER DATABASE x SET COLLATION y doesn’t traverse.  Whatever it is, you’re probably here for the one underlying reason.  Manually consolidating collations would be a nightmare.  So, without loading to much fluff, let’s jump right in.

Here is the basic approach:

  1. Identify all the columns which have incorrect collation values.
  2. Identify all objects bound to above columns, including indexes, keys, checks and UDF’s,
  3. Script out all of the above object DDL.
  4. Drop all of the objects.
  5. Update all the columns with the correct collation
  6. Recreate all of the dropped objects using the DDL generated in step 3

This is done with a collection of non-destructive scripts which generate the DDL to perform all of the above.  This generated DDL MUST be saved to scripts for later use on the target databases.  And obviously, you’ll need one set of scripts per database.  If that requires clarification, just stop reading now.

In this article, I give to you the four scripts to do all this and will try to provide as much understanding as possible.  The first script is purely to create a stored procedure which will be repeatedly called.  This proc will need to exist in a database named “DBADB”.  If you want to use a different database, you’ll need to do a search and replace in the first script and the last script since the database name is hard-coded.  I tried to make these somewhat generic, but the only approach that came to mind would be to use OLE to invoke the VBScript engine to prompt you for the name of the database where this stored procedure is located.  Overkill in my opinion.  Ctrl-H is your friend here.

The script to perform step 3 above uses DMO via OLE, so if you’ve disabled the extended stored procedures; sp_OACreate, sp_OASetProperty, sp_OAMethod, etc, you won’t be able to continue until they’ve been enabled.

One final note.  If you’re not a member of the System Administrators role, please don’t try to execute these.  You’ll probably find your account revoked quickly.

Ok, time to roll up your sleeves.

Script 1 – The DDL Generator

This first script is to create a stored procedure.  This procedure must reside in a location that will not interfere with other databases, whether they’re development, test or production.  The SP uses DMO via OLE to capture a buffer dump of the object code.  The result is the same as if you right-clicked an object and selected “Script to new window as…”

The DMO hierarchy of a SQL instance is weird.  Object reference isn’t particularly intuitive.  But, for the most part, objects are contained within the associated table collection.  In DBA language, that would look like

Table 1:M Checks

Table 1:M Keys

Table 1:M Indexes

Database 1:M UserDefinedFunctions

Therefore, to locate a particular object in the DMO hierarchy, you would need to pass in all the required parameters, including the object type.  So, for checks, keys and indexes, you must also pass in the database name and the parent table name.  For functions, only the database name.


@in_dbName SysName,

@in_objType VarChar(100),

@in_objName VarChar(100),

@in_objSubName VarChar(100) = NULL


Now, the good news.  The subsequent scripts will do all of the above automatically, and this script will be called repeatedly.

Implementing this script.

As I mentioned, this script will be called repeatedly.  The script from whence it’s called will need to be modified to make reference to the correct database name.  These scripts have been designed to work with a DBA-use only database called “DBADB”.  Therefore, if you choose to keep this procedure located in a database of a different name, you’ll need to perform a search and replace for all the “DBADB” reference.  Otherwise, you will need to create a database named “DBADB”.

Execute the first script.  This will create the necessary stored procedure.

Script 2 – Drop Dependencies

The name for this script is somewhat deceiving.  It is a non-destructive script and only performs a set of SELECT statements.  Rather, this script generates a collection of DROP [object] statements.

The goal here is to identify all the objects that are bound to columns which have incorrect collation settings.  There is no clean way to pull them all back in a single statement since determining this is handled differently for the various types of objects.  So, they’re all concatenated together with UNION’s.  The orders in which these are executed are as follows:

  1. Table Functions
  2. Unique Constraints
  3. Check Constraints
  4. Indexes

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

It’s performed in this order in attempt to avoid dependency issues.

The result is a collection of DROP statements.  This must then be copy and pasted into a script and saved for later use.  Best to use a naming convention that reflects the name of the database, and that this will be the first script to execute when you’re ready to begin the patching, for example: myDatabase_post-script_1.sql, myDatabase_post-script_2.sql, etc.

You should take note, it is possible that some databases may not have any objects that are bound to columns that need to be updated, so it’s perfectly fine if this script produces no results.

Script 3 – Change Collations

Much like script 2, this script is also non-destructive and will produce a collection of DDL statements to update the column collations.

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

VERY IMPORTANT.  These scripts were originally intended to allow us to consolidate all our collations in a SQL 2000 to SQL 2008 migration.  Therefore, this script WILL produce two lines of code that you may or may not want.



ALTER DATABASE ‘ + DB_NAME() + ‘ COLLATE ‘ + @collation

where @collation is a variable defined at the beginning of the script.

You can alter the original script I’ve provided, or you can simply remove them from the output before saving.

The remaining results will be a collection of statements to alter the column collations:

ALTER TABLE [tbl] ALTER COLUMN [clm] column_spec() COLLATE [new_collation]

Once this has been executed, copy and paste the output into another script file for later use (myDatabase_post-script_2.sql)

Script 4 – Create Dependent Objects

This is where most of the work takes place.

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

Open the script, connect to the desired database and hit the [fire] button.  Again, like those previous, this script is non-destructive, and produces a series of DDL statements.

The first you’ll notice in the output is the “USE DBADB” statement.  It is very important to note here that if the stored procedure (sp_scriptObject) from the very first script is stored in another database, that this is reflected in the output.

The remainder of the output looks like:

EXEC DBADB.dbo.sp_scriptObject…

With all of the required parameters, based on the type and location of the object.  The other thing you may notice here is that the order in which these statements are in, is the exact reverse of the order from the 2nd script; again, with the intent to avoid dependency crashes.

This output now needs to be copy and pasted to a new query window – not to a file.  The output of the new window should be set to text (Query > Results in Text) or (Ctrl-T).  Execute the statements.  Be aware, that this could take a significant amount of time based on the number of objects and how complex they are.

But, in essence, you will be trapping a buffer dump for each object that needs to be dropped.


There may be a case where an object’s DDL is simply too large.  There are limitations in which the VBScript engine stores string data.  I can’t recall exactly what the issue was, nor do I care to look it up again. But basically, the buffer is limited to 8k.  If your object DDL is greater than 8k, an error will be thrown and the object DDL will NOT be trapped.

After this script has finished, you must first scan the output for errors.  If any are found, identify the object which was not scripted and manually script those and paste them into the output.

This output must then be saved to a final script for execution against the target database.

Final Step

In the end, the above work will have produced 3 scripts that now need to be run against the target database.  Now is a good time to clarify that these generated scripts are very destructive.  So, please take your time, test, test and test.  If you choose to do this on a production box, your career could be at stake.  There is never one-size-fits-all solution, so for each scenario, these scripts may required modifications.  These were produced to fix my needs, but I did try to keep them as generic as possible for re-use.

On your target database, execute each of the generated scripts, obviously in the order specified.

The net result will leave your database with each column, along with the database set at your desired collation setting.

The Quick and Dirty Approach

This section is for those who don’t really care to know the details and simply provides an exact step-by-step.

  1. Backup the target database.
  2. Copy the 4 scripts to an empty folder.
  3. Open Pre-Script_1_DROP_DEPENDENCIES.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  4. Open Pre-Script_2_CHANGE_COLLATIONS.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  5. Open Pre-Script_3_CREATE_DEPENDENCIES.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  6. Create a database named “DBADB”
  7. Execute Pre-Script_0_DBADB.sp_scriptObject.sql
  8. Change the working database to the one which requires collation changes.
  9. Execute Pre-Script_1_DROP_DEPENDENCIES.sql
  10. Save the output to a text file named “dbName_post-script_1.sql”
  11. Execute Pre-Script_2_CHANGE_COLLATIONS.sql
  12. Save the output to a text file named “dbName_post-script_2.sql”
  13. Execute Pre-Script_3_CREATE_DEPENDENCIES.sql
  14. Copy the output to a new query window and execute.
  15. Scan the secondary output for errors.  Identify any failed objects and manually script out the DDL.  Paste it into the location where the error occurred.
  16. Now, save this output to a text file named “dbName_post-script_3.sql”
  17. Copy dbName_post-script_1.sql, dbName_post-script_2.sql, and dbName_post-script_3.sql to the target machine.
  18. Connect to the desired target database and execute each of the above post-scripts in numeric order.

Good luck.  Please heed any warnings I may have mentioned.  If you have any questions, certainly send me an email and I will try to answer promptly.  But, also please be patient, I do have a full time job that takes priority.

Brian Cidern
brians.sql.blog (at) gmail (dot) com

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Whitepaper – Auditing in SQL Server 2008
Next Post
SQLAuthority News – PASS 2009 Sessions on Query Optimization and Performance Tuning

Related Posts

29 Comments. Leave new

  • Jon Wickström
    May 4, 2010 7:22 pm

    Hmm… Does this work on MSSQL 2005?

  • Rene Moosens
    July 8, 2010 7:57 pm

    I let it work on MSSQL 2008.

    One remark, I had to run sp_configure to enable ‘Ole Automation Procedures’ during generation of the CREATE script.

  • I tried it on sql server 2005. the first two scripts ran find, but when I ran the third script, Pre-Script_3_CREATE_DEPENDENCIES.sql, I kept getting

    ERROR: Connect for each call.

    I am sysadm, and do not know what is wrong. I need to have something by friday, and am researching another method that does about the same thing, but is more manual, and is taking much longer. any suggestions as to what I am doing wrong would be appreciated.

  • Thx,

    This was a very helpfull article and scripts.

    There are 2 errors
    1: in de Usage.txt it says you need to create the database named ‘DBA_Data’ wich should be DBADB (as mentioned in the article)
    2: when you use these scripts in 2005, you have to change

    After this it works like a charm.

  • Hello all.
    This is the author of the article and have been meaning to publish a couple bugs I’ve discovered.

    Script: Pre-Script_0_DBADB.sp_scriptObject.sql
    Line 81
    Should be changed from:
    EXEC @rc = sp_OAMethod @o_server , ‘Connect’, NULL, ‘(local)’


    EXEC @rc = sp_OAMethod @o_server , ‘Connect’, NULL, @@SERVERNAME

    The above corrects the “connect” issue when running the scripts against a named instance.

    These scripts were technically designed to run on a SQL 2000 instance, where the generated DDL could then be run on any version. So, technically this isn’t an error. However, I’ve found that these scripts do run fine on SQL’05 and SQL’08. However, Microsoft dropped DMO in 2005 and replaced it with SMO. So, in order for these to run, you may need to install DMO with the Backward Compatability package which can be downloaded at:

    @CJ – Thanks for spotting that. Sounds like you’re current collation is Case Sensitive. So, by correcting the case for .COLUMNS, it makes the script much more generic – exactly what I was trying to achieve.

    @Kevin – Apologies for not posting this sooner. Your connection issue is addressed in my first bullet point above.

  • Hi sir,
    I can’t download the scripts. If the address of download-able files have been changed?

  • Hi,

    The database I need to convert used alot of user defined types, to support this I changed the select in script 2 as follows. Basically it checks the DOMAIN_NAME which on my system contained the name of the user defined type for the column. (User defined types appear to take the default collation of the database so this is not specified)

    DATA_TYPE IN (‘text’,’ntext’) THEN
    ‘ COLLATE ‘ + @collation
    ‘(‘+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+’)’ + ‘ COLLATE ‘ + @collation

    COLLATE Latin1_General_CI_AS
    TABLE_NAME N’dtproperties’ AND
    COLLATION_NAME @collation

  • Hi

    Another issue I has with these scripts was that FOREIGN KEY CONSTRAINTS generated duplicate entries on script 1 and 3, to solve this I added a Distinct to the relevent select statements.

    Other than that these scripts worked great, Cheers for publishing them.

  • kevin benedict
    January 20, 2011 8:39 pm

    thank you. I appreciate your work.

  • Thank you! You saved my day! ;-)

  • Ronald Kraijesteijn
    July 13, 2011 3:40 pm

    Thanks a lot! I become crazy of this collation stuff after migrating to a new data center with database consolidation of multiple users. I am trying to use your script so solve this problem!

  • Ronald Kraijesteijn
    July 13, 2011 6:40 pm

    I am having problems with the pre-3 script. I am getting these kinds of errors:

    ERROR: Connect.
    Source: ODSOLE Extended Procedure
    Description: [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
    [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

    Any idea how to fix this? I am sysadmin on the server.

  • Ronald, please see my follow-up reply above (September 28, 2010). This will likely solve the connection issue you’ve described.

  • The link to the scripts no longer works – can this be fixed please??

  • Hi, the link to the code no long works. it is directed to an “unknown” page.
    Can this be fixed please.


  • Good Afternoon:

    This information is very helpful. If you would be able to fix the link to the downloadable code and documentation, it would be very much appreciated.

    Thanks and have a great day,

  • HI, The two first script that are generated work well (one to drop constraints and index and one to change the collation). But the third one to create indexes and contraint does not work for me. I have this error :

    Error : Load Object, dump buffer
    I am on SqlServer2005

    Can someone help me?

  • Hello, Can you fix the link, please?

    Maria Pia Fernandez

  • I think the correct download is

  • Hi, These scripts have worked well for me, with some changes for computed columns. However, this does not update the collation of the relevant columns in views as defined in INFORMATION_SCHEMA.columns . Has anyone seen problems caused by this?


Leave a Reply