SQL Server 2000/2005 Ent Mgr will allow you to jump into table designer and
change a column type.  2005 will capture the script, which involves renaming
the table, generating a new schema and then re-inserting the values.
  I have to perform this on some remote instances and won't know the schema
ahead of time.  Is there a tool to change schema types (in-place) that allows
me to avoid making the change interactively??

ChrisA (Chr***@discussions.microsoft.com) writes:
First of all, is the change such that you can do it with ALTER TABLE?
Next question, is it suitable to do it with ALTER TABLE?

Implementing database changes is no kids game, but takes careful planning,
and the toys^H^Hols in Ent Mgr/Mgmt Studio are dangerous for this sort of
thing, but so can ALTER TABLE be.

If the channge is only a metadata change, like changing from varchar(10)
to varchar(20), then ALTER TABLE is swift as the eye, and what you should
use. But if the change requires the table to be rebuilt, then ALTER TABLE
will take the table offline for some time, which may or may not be
acceptable. Whether your change is of the latter kind is something you
should test.

And then there are changes that are not possible to make with ALTER
TABLE at all, because existing values needs to be transformed.

When you reload a table, there are a number of steps to take:

1) Create the new definition under a temporary name, including a
   temporary name for the primary key.
2) Copy data over.
3) Move referencing foreign keys.
4) Drop the old table.
5) Rename the table and the PK to the correct name.
6) Restore indexes.
7) Restore triggers.
8) Restore foreign keys, check constraints and defaults.

There are a couple of possible way to vary this theme.

What you get in EM/Mgmt Studio does this, but never execute directly
from the GUI, but save and carefully review the script. There are a
couple of transformations you need to apply:

1) Do a search/replace on WITH NOCHECK to WITH CHECK. This will cause
   the application of the constraints take longer time, but the optimizer
   will be able to trust the constraints, which can have drastic effects
   on performance
2) Remove all BEGIN and COMMIT TRANSACTION except for the first and
   the last.
3) Remove all "go" in the script.
4) Embed all statements that are not execution of stored procedures in
   EXEC().
5) If you are on SQL 2005, and BEGIN TRY END TRY around the entire
   script and in the CATCH section add a ROLLBACK TRANSACTION

The transaction stuff could cause the transaction log to explode if
the tables are huge. You can skip it, if you decide to restore a
backup on any error.

Also, if you work in the GUI and find that you have been working in
the wrong table, close and start over. Else you may find that the
Table Designer includes the change anyway in the script.

It is not unlikely that there are better tools out there, but whatever
tool you use, you should still carefully review what is generated. As
I said, this is no kids game.

Erland,
  Thanks -- that reply would be a good beginning to a dba book's chapter on
schema modifications.
  The best approach seems to be to use the new Mgmt Studio and capture the
scripts as a beginning.  I'm mostlt adding columns, so it's fairly safe.  The
thing I was hoping for a solution for was changing some Floats to
SmallMoneys.  Mgmt Studio was dropping Constraints, creating temp tables and
copying back and forth.  But it looks like I might get away with just the
Alter.

  Thanks -- Chris

ChrisA (Chr***@discussions.microsoft.com) writes:
Beware that it is only if you add a nullable column as the last column
in the table Mgmt Studio will use ALTER TABLE. It will not use ALTER
TABLE to a non-nullable column with a default value, even if SQL 2000
and SQL 2005 supports this. The Table Designer is so old that it supports
only SQL 6.5 syntax.

I'm not sure this is always the best approach... Management Studio's table
designer can teach you some very bad and unnecessary things.

A

Database Restore Error
Can not save relationship
Looking for Database comparison/synchronizing tool
SQL 2005 SMO Scripts
SQL 2005 Question