ALTER TABLE

I sometimes use the ALTER TABLe to add certain fields in my table. I need to
do it programatically. I will not get into why eventhough I have access to
Enterprise manager and can use that to do it that way.
My question is I'd like to know if there is syntax that I can use when I
ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the tabl
e
to place this new column. For example placing the new field in 3rd position
or place in a table of 20 fields. I'd like to specify where in the table to
place this new field...
thanks in advance...AFAIK you can not
If you look in EM when you do it (save change script icon) you will see that
EM creates a new table drops the old one and renames the newly created one t
o
the original one
http://sqlservercode.blogspot.com/
"Angel" wrote:

> I sometimes use the ALTER TABLe to add certain fields in my table. I need
to
> do it programatically. I will not get into why eventhough I have access to
> Enterprise manager and can use that to do it that way.
> My question is I'd like to know if there is syntax that I can use when I
> ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the ta
ble
> to place this new column. For example placing the new field in 3rd positio
n
> or place in a table of 20 fields. I'd like to specify where in the table t
o
> place this new field...
> thanks in advance...|||And at the same time preserving the data?
"SQL" wrote:
> AFAIK you can not
> If you look in EM when you do it (save change script icon) you will see th
at
> EM creates a new table drops the old one and renames the newly created one
to
> the original one
> http://sqlservercode.blogspot.com/
>
> "Angel" wrote:
>|||Column order is completely irrelevant. If you need this for presentation
purposes, simply do it on the client. If you need this for documentation, us
e
the INFORMATION_SCHEMA system views.
ML|||> I'd Like to specify where in the table
> to place this new column.
Sorry, you cannot do this with ALTER TABLE.
You can, of course, try to do what Enterprise Manager does:
http://www.aspfaq.com/2528|||> And at the same time preserving the data?
Yep, it does. See http://www.aspfaq.com/2528|||Well if you look at the change script you will see that the isolation level
is SERIALIZABLE
This is the highest level, no updates,inserts or deletes can happen on this
table while this script runs
http://sqlservercode.blogspot.com/
"Angel" wrote:
> And at the same time preserving the data?
> "SQL" wrote:
>|||Assuming you can get this to work, don't forget to use sp_refreshview on all
the views based on the table you alter. If you don't, your views may start
returning unexpected results.
"Angel" wrote:

> I sometimes use the ALTER TABLe to add certain fields in my table. I need
to
> do it programatically. I will not get into why eventhough I have access to
> Enterprise manager and can use that to do it that way.
> My question is I'd like to know if there is syntax that I can use when I
> ALTER TABLE and ADD COLUMN to a table. I'd Like to specify where in the ta
ble
> to place this new column. For example placing the new field in 3rd positio
n
> or place in a table of 20 fields. I'd like to specify where in the table t
o
> place this new field...
> thanks in advance...|||On Thu, 22 Sep 2005 09:37:07 -0700, mike wrote:

>Assuming you can get this to work, don't forget to use sp_refreshview on al
l
>the views based on the table you alter. If you don't, your views may start
>returning unexpected results.
Hi Mike,
AFAIK, that's only necessary for views defined as SELECT * FROM ...
And since you shouldn't use SELECT * in production code anyway, there's
no need to worry.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)