Changing database collations with Powershell

Published 07 March 11 09:02 PM | MartinBell

Changing a database collations is rarely as simple as it may seen. You may have found articles which show you the ALTER DATABASE statement such as Example B, unfortunately that example only works because you have just created the testDB and nobody has connected to it!

Anyone who has tried to do this, will soon find the reality is that it is far from simple. Looking through the newsgroup/forums or just going through the pain of doing it yourself will make you realise that to fully change a database collation you will need to propagate the collation to all character based columns in every table. This can be a huge task so using Powershell seems an obvious choice to make the task easier, and example scripts such as this will show you how to change the collation of a column..

If you are very very very very lucky that is all you will need to do, but looking at shows that it will rarely be that simple. Character columns that have statistics, primary keys/indexes or check constraints will require them to be dropped before the collation can be changed. What’s more, if there is a foreign key referencing the index or primary key or a full text index using an index, they also have to be dropped before the primary key/index. Views or functions that are schema-bound have to be removed before changing the table they are bound to.

Anyone who has tried to solve this problem themselves may find that they are just moving from problem to problem without getting much nearer to solving it. After a while it may seem easier to give up and resort to creating a new database and schemas with the correct collation and then transferring all the data.

As an exercise I decided to try and write a script in Powershell that would do this frustrating and tedious task. My test database was the Adventureworks2008 database, so any issues that don’t manifest themselves during the testing may not be included!! If you do find anything that could improve the script let me know.

Initially I started out identifying the columns that had the database collation. The procedure Process-Indexes will all identify Indexes/Primary Keys that include columns with the database collation. Process-Statistics identifies columns with the database collation that have statistics created on them. Process-ForeignKeys will determine which Foreign keys and Process-Fulltextindexes will determine which Full Text Indexes need to be removed before the Indexes/Primary Keys are dropped. Using this method only the minimum number of changes are made and indexes aren't dropped or recreated when it isn't necessary. When I wrote Process-CheckConstraints it used a similar method, but when I tried to change the database collation I had additional errors referencing other constraints not removed by the function
, so I changed it to use a sledgehammer approach and removed all the constraints. Similarly Process-SchemaBoundFunctions and Process-SchemaBoundViews drop all schema-bound functions and views, as I would expect only a few of these to be present in a database dropping all of the schema-bound objects should be an issue.

Process-ComputedColumns will drop all computed columns because some computed columns that did not have the collation set in sys.columns still caused errors when trying to change the database collation. By dropping and recreating these columns then the column order in some tables may change. If you have some poorly written code that relies on the column order, then this will be an issue. The solution are either, change the code (my prefered solution if possible!) or re-create the table specifying the columns in the order you want. I will leave this up to you, but to help I have written the position of the computed column in the log file.

The functions Process-DependentCheckConstraints, Process-DependentFunctions, Process-DependentProcedures, Process-DependentViews, Process-DependentTriggers uses sys.sql_dependencies to identify which related objects need to be scripted and dropped before the columns can be changed. This should catch most of the objects that needs dropping but can't be not identified by the collation. Originally I had used Invoke-Sqlcmd as a cursor as shown in my previous blog post, SQL Server MVP Ben Miller suggested I use a dataset returned from ExecuteWithResults as this would use the existing connection. Ben also suggested using the hash table to obtain the unique list of objects to drop.

Once all constraints have been removed Process-Columns can then change the collation of all the columns in each table that has the database collation and Process-Database will change the database collation itself. Originally Process-Database used invoke-sqlcmd as I found it easier to implement than using SMO, but after re-visiting the problem I managed to get it working!

Process-SQLFile will run a given SQL script. This will allow the re-creation of the constraints etc. that had previously been scripted and removed.

Once I had created these functions it is then a matter of calling them in the correct order!
The full script to change the collation can be found HERE!!.

Simple help has been included in the script so using the command:

get-help .\Collation-Change.ps1

will give you information about the script and

get-help .\Collation-Change.ps1 –Examples

will show you examples of how to call the script. The script can use either Windows or SQL Server authentication. If the –Username parameter is specified SQL Server Authentication will be used.

The script comes with the usual “Health Warning” it has had limited testing. Always try the script on a backup of your database before running it against a live system. Due to the changes made by the script, I advise you any replication, log shipping, snapshots and database mirrors are removed. The script has not been tested with these in place. The database should be in simple recovery mode to avoid the log file growing too large, so restoring a backup will be the fallback recovery position. Make sure you can recover from this backup before running the script.

Thanks to Ben Miller for his invaluable help putting this post together.

** Update **

Even though I have not been able to re-create the problem experienced by JMAlvarez (see comments) the change so that you pass the server object to the scripter rather than the name should be made as it is expecting a server object (see here).~

During testing this change I have found that the SMO Statements can time out and processing the SQL Files (particularly the indexes) can also time out, so I have changed the script to include the timeout parameter. This may require changing depending on your environment/database.

I have also changed the processing of computed columns as this had errors with computed columns being duplicated in the column collection. I think this problem may have been due to the database, but the code is fine for all databases.

I have updated the script to work on SQL 2012 and include the sqlps module instead of loading the SqlServerCmdletSnapin100 snapin.

Filed under: ,


# Martin Bell UK SQL Server MVP said on March 16, 2011 09:54 PM:

A post that shows that the collations for columns in a foreign key relationship must match the collations of the columns of the index that they refer to

# Martin Bell UK SQL Server MVP said on March 17, 2011 11:29 PM:

Sometimes and example is the quickest way to see how to do something. Unfortunately when you can find one it can make it very difficult to work out what to do. SMO enumerations was one example of this so when I worked out what to do I submitted the example

# JMAlvarez said on November 11, 2011 04:26 AM:

Excellent script. I've used it twice and it worked perfect.

There is an small error in the script that is repeated in all functions that generate the TSQL scripts. It only occurs when connecting to the database using a username and password for SQL Server authentication .

On the line where says

$Scripter=New-Object ("Microsoft.SqlServer.Management.Smo.Scripter") ($srv.Name)  ;

Should read.

$Scripter=New-Object ("Microsoft.SqlServer.Management.Smo.Scripter") ($srv)

The "Smo.Scripter"  object  constructor parameter is an object of type "Smo.Server" and if not valid it  try to use Windows credentials.

Please, apologize my english.

# Blog de Jose Mariano Alvarez said on November 11, 2011 05:10 AM:

Cambiar el COLLATION de una base de datos y todas las columnas

# SCCM 2012 failing pre-req check (SQL Perms) said on February 28, 2012 07:38 AM:

Pingback from  SCCM 2012 failing pre-req check (SQL Perms)

This Blog

SQL Blogs