changing collations

[Sai]

Hi,

We have around 150 databases as case sensitive, and we are planning to
change it to case insensitive. Each database has around 180 tables, I
have changed the collation on DB, but changing collation manually on
each column is a daunting process. Is there any script or tool which
can assist in doing this.

Appreciate your help.

Thanks
SAI

[Erland Sommarskog]

Sai (sb********@gmail.com) writes:

We have around 150 databases as case sensitive, and we are planning to
change it to case insensitive. Each database has around 180 tables, I
have changed the collation on DB, but changing collation manually on
each column is a daunting process. Is there any script or tool which
can assist in doing this.

I would probably bulk out data, and build the databases from scripts and
bulk back.

If you want to use ALTER TABLE, I think you still need to script all
indexes, as you cannot change the collation on an indexed column, if I
recall correctly. You could run queries on the system catalog to generate
ALTER TABLE ALTER COLUMN command. I'm not including this right now, as I
am in bit of hurry.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Sai]

Bulk out data is also an option, but its around 150 DBs and I am trying
to do it in minimum time, this would take long time. I am planning to
minimize the impact to customers while doing so. ALTER TABLE ALTER
COLUMN is also an option, but its very risky as it will drop the
primary keys and indexes which we have to create manually. Thats why I
am looking for an automated tool or some script which does it.

Anyway thanks for the options.

[Erland Sommarskog]

Sai (sb********@gmail.com) writes:

Bulk out data is also an option, but its around 150 DBs and I am trying
to do it in minimum time, this would take long time. I am planning to
minimize the impact to customers while doing so. ALTER TABLE ALTER
COLUMN is also an option, but its very risky as it will drop the
primary keys and indexes which we have to create manually. Thats why I
am looking for an automated tool or some script which does it.

As far as I know, ALTER TABLE ALTER COLUMN does not drop the indexes,
you will have to drop them manually.

I would not expect there any tool to be available for anything of this
large scale. Doing it for once database is not that difficult: script the
indexes, drop them, create the ALTER TABLE commands, and run the index
scripts. But of course doing this 150 times manually, is error-prone.

It could be worth the investment to look into using DMO for the task.
This is probably the best bet, as I believe DMO includes scripting
functions. (I have never used DMO myself, though.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alex]

I wrote a tool that generates a script to change the collation of each
column in the database

http://www.codeproject.com/useritems...eCollation.asp

I ran this agains a 20 GB data warehouse and it worked fine for me.

You may want to enhance the code a little to script all 150 databases
automatically

Alex

[Sai]

Thanks for the tool, but I have lot of problems running it on my
current database. Its good I have the source code, I will modify the
code and send it to you back.

SAI