Removing Collations

Dante

Hi

I have several databases on a remote servre where the server was set up with one collation order and the fields in the databases were set up with a different collation order - because i forgot to remove the collation settings from the creation scripts

Now, some weeks later, I have to remove all the collation definitions of all the fields in those databases so that they revert to the server default collation order. This is to avoid the occasional collation order conflicts that arise in stored procedures.

So far as I know, non of the affected fields are key fields as I remember this would stop me changing the collation.

So what I am looking for is a neat method to generate ALTER TABLE... ALTER COLUMN scripts that will keep all the column details as they are except that there will be no COLLATION ORDER definitions.
There are over 100 tables in eacg database and lot sof nvarchar fields ..

Any ideas much appreciated.

Dante

Daniel Handley

Hi Dante

There is an option "ignore collation order" that will prevent any collation information being included in the create and alter commands within a generated script.

Regards
Dan

Dante

Hi Dan

Yes, thanks, I know about this option.

Its easy to generate a script to CREATE every table and column in a dtabase, but what I don't see is how to generate a script that will ALTER every column in an existing database to make them the same as they are (except for the collation).

If I could generate that script then I could easily remove the collation part of it.

David

Daniel Handley

Hi David

The product will use alter by default to try and keep overheads low and availability high. The create command will only be used when the alter command is unavailable for the situation.

The problem you have with the collation order is not going to be sorted by using SQL Compare. You will need to create a script that alters all the objects to the default collation of the database.

Let us know how you get on.

Regards
Dan

Dante

Hi Dan

I had hoped you might come up with a solution that I had missed.

Thanks for looking, anyway

I've found a manually intensive but simple way of changing the collation order on a field in Enterprise manager. So I might just copy a database, then manually change the collation every char field in every table in the copy. Then I can use SQL Compare to produce the ALTER script for all the "real" databases.

Or I will generate a complet set of table CREATE scripts and attempt some sort of WSH or macro to convert them to ALTERING the char (nvarchar, ntext, etc. fields) and removing references to other field types...

Either way, it's an afternoon's work (at least). I'll let you know how it goes.

Thanks

David

Daniel Handley

Hi David

If you are going to start from scratch again you may as well create a blank database and use SQL Compare with the "ignore collation order" option. This will then create the schema using the new database default collation. The data can then be transferred across using SQL Data Compare.

Regards
Dan

Dante

Hi Dan

I'm not starting from scratch. The databases are in production use. I need to end up with a single simple, relable script that I can run on our test systems, then on the production systems one weekend after a full backup(!).

David

Daniel Handley

Hi David

Sorry my mistake. Let us know how you get on and if we can be of any assistance.

Regards
Dan