Posted by BD on 09/14/05 03:06

I am asked to do this on a very regular basis.

The logic I normally go through is as follows:

-delete trigger for the original table (if it exists);
-delete all views for the original table, if they exist (they will keep
referencing the original table even after it is renamed)
-rename the original table to table_name_old (executing the native
sp_rename stored procedure)
-create the new table in the correct structure
-create new default value constraints unless you're okay with system
generated names for default value constraints
-insert into new_table (all columns except for new one) as select (all
columns) from original table
-recreate view(s) if applicable
-recreate trigger if applicable
*then confirm all is well before dropping the old table.

I would recommend making a SQL script for this process, and keeping it
as a template for future use.

I also would recommend SQL scripts over EM or any other GUI: All that
GUIs will ultimately do is provide a nice pretty set of screens and
buttons, and then generate a SQL script which you may or may not see.
Getting used to coding the SQL script will eliminate the middle-man,
and allow you to comfortably replicate the same process again and
again.

I can fire you a sample script with the logic that I use if you like.

[Back to original message]