ALTER TABLE ADD

[SemperFiDownUnda]

I'm writing a script to modify an exsisting database to a more appropriate structure.

Basically I'm altering the table so the primary key is not user entered but just an IDENTITY column.

Everything works but I would like my new primary key column to be the first physical column in the table. By default the ALTER TABLE ADD puts the column at the end of the table. In EM you can insert columns so that they can be at any point in the table. Is there a way to script that withouth creating a new table with the definition you want, copy the data across, drop the old table, rename the new table?



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[bowline]

Well, Enterprise Manager is not really inserting that column for you - it is recreating the table.

When you save your changes, it will:
- Create a tmp_XXX table with your new table structure
- Copy any data from the existing table over to the tmp_XXX table
- Drop the existing table
- Rename the tmp_ table to remove the "tmp_"
- Reassign permissions, etc.

...kind of a pain...

 

[vongrunt]

AFAIK physical insertion of column cannot be done without recreating entire table. Plus there are many dependencies - indexes, foreign keys, defaults, column statistics etc. Go to EM, make changes you want then click "Generate SQL Script" to see what it does.

 

[SemperFiDownUnda]

Enterprise Manager doesn't let you generate scripts for potential changes. Ie you can't generate the scripts that EM would use to make the changes.

I was just wondering if anyone knew another way. I won't bother with the method I described because it is not worth the effort.

Thanks for your inputs.


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[vongrunt]

> Enterprise Manager doesn't let you generate scripts for potential changes.

Mea culpa... not "Generate SQL Script". Right click on table, Design Table. Make changes you want but don't save. Third icon in a toolbar row = "Save change script".

I'm also wondering if anyone knows another way - on large tables this method can be painfully slow.

 

[SemperFiDownUnda]

ah thanks


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first