Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Changing Rogue Database Collations

By Mike Pearson,

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.

Total article views: 12558 | Views in the last 30 days: 11
 
Related Articles
FORUM

Script to Change Collation of User Database

without affecting master databases & other user databases collation

SCRIPT

Show all database's tables' columns' collations.

Created view displays all text columns' collations in the current database with the information whet...

ARTICLE

Beware of Mixing Collations: Converting Collations

With SQL Server 2000 you are able to create databases or columns with a different collating setting ...

FORUM

Collation

Incorrect Collation

SCRIPT

Change the collation of Columns

This script changes the collation of columns to default database collation in all the tables

Tags
administration    
miscellaneous    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones