Alter Database Move column

Hi,
Is it possible to move a column using an SQL script?
I have looked at the Alter Table syntax help but can't see any indication of
ordinal control .
I have a table that I have had to add an indentity column to.
I want to move it to pos 0.
My script so far is :
alter table MasterStationTest
drop constraint pk_masterstationtest
go
alter table MasterStationTest
add id int
IDENTITY(1,1)
PRIMARY KEY
go
thanks
BobNo, you have to drop and re-create the table for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>|||Hi,
No , You cant change the column position using ALTER table statement. The
only solution is:-
1. Create a new table with new structure with identity property
2. Insert data into the new table from actual table (Execlude the col1 which
is identiy)
insert into new_table(col2,col3...coln) select col1,col2,col3...coln from
actual_table
3. Generate Script for indexes and dependant objects
4. Verify all the data are moved successfully
5. Drop the actual table
6. Rename the new_table to actual table using (sp_rename new_table,
actual_table)
7. Create the index in the table (execute the script generated in step-3)
Note:
Enterprise manager will do the above steps when you change column postions
or add columns .....
Thanks
Hari
MCDBA
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:#cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>|||Although you didn't ask, Bob, it is often considered to be a flaw when we
depend on the physical ordering of the columns in a table... When I first
started doing this stuff many years ago, I tried to keep columns in tables
in a particular order, and found myself dropping/recreating tables all of
the time ( all on nights and weekends as well).
I encouraged the programmers to always use a column list and life got
better...
I'm not trying to tell you how to do your job, just making an observation
you might find helpful...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Clegg" <bclegg@.clear.net.nz> wrote in message
news:%23cIRcW9PEHA.3304@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to move a column using an SQL script?
> I have looked at the Alter Table syntax help but can't see any indication
of
> ordinal control .
> I have a table that I have had to add an indentity column to.
> I want to move it to pos 0.
> My script so far is :
> alter table MasterStationTest
> drop constraint pk_masterstationtest
> go
> alter table MasterStationTest
> add id int
> IDENTITY(1,1)
> PRIMARY KEY
> go
> thanks
> Bob
>