A few months ago Greg Larson did an excellent article

(http://www.sqlservercentral.com/columnists/glarsen/collate_part1.asp) on the perils of mixing collations. 

For those of you not familiar with collations, although you can specify collations for each character column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement, the database will always retain the collation with which it was created. Alas, there is no quick-fix like ALTER DATABASE SET COLLATION = 'Blah'.

Well, actually, the command does exist, but although it will change the default collation of the database, system tables and variables in stored procs, you will still have your existing tables to worry about, along with their indexes, constraints, and referencing objects and so on. Unfortunately, as tempting as it may be to consider running a command like…

ALTER TABLE <TableName>
ALTER COLUMN <ColumnName> varchar(50) COLLATE <CollationName> NOT NULL

…it just won't cut it either, because

a) you’ll have to do this for every character column in every table of your database;

b) any objects such as indexes etc that reference that column will generate the error, “ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column….”

Now, I don’t know about you, but I’m not brave enough to get into an endless cycle of dropping and re-creating these table objects so that I can get into equally longwinded cycle of altering columns one at a time. Sounds like Cursor-Hell to me. So, here’s the way I chose to tackle this problem – it worked for me and it was great, enabling me to clean up an average of about 25 databases per day. However, I fully realise that every site and DBA is different – so see this article as a framework rather than a firm and fixed “how-to” document. And for the sake of the example, let’s assume one of my databases is called

Southwind.

Step 1 – Move and rename

The first thing I'll do is to detach all the rogue databases, move them from \MSSQL\Data to \MSSQL\Data\Old, and re-attach them an ‘_Old’ suffix – for example Southwind will become Southwind_Old. This enables me to create a new Southwind database with the correct collation, and then repopulate it with the data from the original Southwind_Old database.

Step 2 – Create the new database

Generate scripts of the Southwind_Old database and all it’s objects, selecting a full house – extended properties, indexes, triggers – the whole lot. However, when you do this you notice that any CREATE TABLE snippets will still reference your old collation – for

example: 

CREATE TABLE [dbo].[Categories] (
      [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
      [CategoryName] [nvarchar] (15) COLLATE <OldCollation> NOT NULL ,
      [Description] [ntext] COLLATE <OldCollation> NULL ,
      [Picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

To get around this, just before running the script in query analyser to create the new database, use the replace command (CTRL+H) and replace the phrase "COLLATE <OldCollation>" with "" (Nothing). That way, when this script fires, it will use the default server collation instead. Thus our script above

becomes: 

CREATE TABLE [dbo].[Categories] (
      [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
      [CategoryName] [nvarchar] (15) NOT NULL ,
      [Description] [ntext] NULL ,
      [Picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

A small note : When generating the script, if you select ‘Only script 7.0 compatible features’ from the formatting tab of the Generate SQL Script dialog box, that will drop any references to collations. HOWEVER – it will also drop your extended properties – so be very wary about using that option. You DO use extended properties, right?

Also, if you included the ‘create database’ option in your script – don’t forget to check and replace the database name – you don’t want to go dropping and recreating the Southwind_Old database and it’s objects just yet!

Once this script has run – I now have Southwind which is schematically identical to Southwind_Old except for the collations. Great. Now to get the data in there, and finish up the job.

Step 3 - Populate the new database

At the end of this article is the script that I used for this task (Script

1). As per the instructions in the greensheet of the script, all you need to do is run it from within the new (Southwind) database. This script will generate a new script that takes care of all the FK hierarchies, relationships, triggers, primary keys, constraints etc. All you need to do is copy from the results window and run it in a new window. It will then copy your existing data across by using INSERT INTO etc.... so if you have large databases, take the necessary precautions with regards space, log files etc.

Then, just for that extra peace of mind there is a script (Script 2) which does a checksum on the tables, and will report if there are any errors. I had a 100% success rate - so it worked for me.

Step 4 - Database Diagrams

All database diagrams are stored in a sytem table called dt_properties. More than likely your new Southwind database won't have this table in it unless you initiate the process of creating a new database diagram, and then SQL will automatically create this table. If you do have diagrams in your Southwind_Old that you need in Southwind, then this script will do the

trick.

SET IDENTITY_INSERT [Southwind].[dbo].[dtproperties] ON
GO
INSERT INTO [Southwind].[dbo].[dtproperties]([id], [objectid], [property], [value], [lvalue], [version], [uvalue])
SELECT [id], [objectid], [property], [value], [lvalue], [version], [uvalue] 
FROM [Southwind_Old].[dbo].[dtproperties]
GO
SET IDENTITY_INSERT [Southwind].[dbo].[dtproperties] OFF
GO

Step 5 - Crack open a beer.

That's it. 4 quick steps and the job is done. Like I said earlier - there will probably be as many different ways to do this as there are DBA's - but this was the way I did - and it worked just fine for me. All that's left to do now is backup and drop Southwind_Old, and your new, properly collated database is ready to use. Serve chilled.