Re: changing collations

Sai (sbillanuka@xxxxxxxxx) 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, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: ALTER IDENTITY setting...
    ... Enterprise Manager would do. ... I also would suggest you use EM and save the script. ... Pro SQL Server 2000 Database Design - ... > I just need to alter column with Identity. ...
    (microsoft.public.sqlserver.programming)
  • Re: automate tool to change column type?
    ... schema ahead of time. ... is the change such that you can do it with ALTER TABLE? ... but save and carefully review the script. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • Re: Management Console error when Inserting new column
    ... This is on a SQL Server 2005 Standard Edition. ... and in the script specifying the values for the new column. ... This is exactly what the table designer does, ... ALTER TABLE tbl ALTER COLUMN datatype NOT NULL ...
    (microsoft.public.sqlserver.server)
  • Re: How to alter a Varchar column to a shorter length (with data in it)
    ... Then run your alter table command. ... I support the Professional Association for SQL Server ... > The following script gives me a erro and aborted. ... Alter table Unit alter column Unit_Name varchar null ...
    (microsoft.public.sqlserver.server)
  • Re: Problem altering table and adding a default
    ... ALTER TABLE dbo.Test_tbl ADD Notnull_col1 charNOT NULL ... SQL Server does not allow that - you can only add nullable columns. ... After you add a new column in Enterprise Manager, uncheck Allow Nulls, bind the uder- ... Start Profiler and perform a new trace. ...
    (microsoft.public.sqlserver.server)