Posted by Erland Sommarskog on 12/22/05 00:52

Sai (sbillanuka@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, esquel@sommarskog.se

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

[Back to original message]