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.

sp_scriptObject(

@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() + ‘ SET COMPATIBILITY_LEVEL=100

and

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 2^nd 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.

VERY IMPORTANT.

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)