WillHanna avatar image
WillHanna asked

How to change Collation for ALL objects in a database

Just restored a database from another site and running into all sorts of problems to do with Collation
Is there any 'easy' way to just completely change Everything in a database to a specific collation
e.g. Tables, Columns, Indexes, Constraints,. in other words everything
Something like: Change Database MyDatabase to Collation Latin1_General_CI_AS

(I cannot find ant 'simple way to do this

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

srutzky avatar image
srutzky answered
I cannot find a simple way to do this

That's because there is no simple way to do this. Collations can be rather tricky, and even more so when trying to change them, and even more so when trying to change them when there is existing data (which I assume is the case here).

There is no command to change the collation of all objects (columns, indexes, etc). The ALTER DATABASE ... COLLATE statement will only change the default collation of the database, which controls most of the DB-level meta-data (object names, etc), collation of string literals / variables / parameters, and column collation for new / altered string columns that don't specify the COLLATE clause.

You have two options:

  1. Export all data (if there is any), script out all objects, script out database, drop and recreate DB (with desired collation, of course), recreate all objects, import all data (if there was any)

  2. Use an undocumented (officially) command that changes the collation of all columns at the meta-data level. I documented this approach here:

    Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Option 2 is by far the quickest and easiest approach, BUT it does not work in all situations, so you need to be rather careful with it. This method does not work if you have VARCHAR data in the 128 - 255 range and are changing code pages (it will likely corrupt characters in the 128 - 255 range). There are some types of objects that do not get updated, such as User-Defined Table Types and Partition Functions (of course, those only matter if you are using strings). Please review my post to see if your scenario has any problem areas. If not, you can likely set up a temporary instance of any random collation (just not the one you want to convert to), restore the affected DB, go through the sqlservr -q process to convert to the desired collation, backup the DB, and restore the updated DB where it should go. And then TEST, TEST, TEST!!

For more info on working with collations / encodings / Unicode, please visit my site: Collations Info

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.