Adding a Column in the middle

Can someone give me the syntax for adding a column into
the middle of the table? I know how to do this in E.M,
but I need to script this.
Hi,
You cannot add a new column in the middle of the table structure.
The only solution to add the column in the middle:-
1. Down load the data
2. Script the table structure and dependant objects like indexes, Triggers..
3. Recreate the table with new structure (Add the new column in the position
u require)
4. Execute the dependant objects scripts
Note:
If you add the new column using enterprise manager it does the above steps
in back end.
Thanks
Hari
MCDBA
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
|||Here is how to do it. This is exactly what EM does just to let you know. I
got this via using profiler. Basically I create a table first with three
column, a, b, and c. Then I wanted to add column d prior to c.
CREATE TABLE dbo.Tmp_Table3
(
a char(10) NULL,
b char(10) NULL,
d char(10) NULL,
c char(10) NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.Table3)
EXEC('INSERT INTO dbo.Tmp_Table3 (a, b, c)
SELECT a, b, c FROM dbo.Table3 TABLOCKX')
DROP TABLE dbo.Table3
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=16388
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Table3'',
N''Table3'', ''OBJECT''
', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.
|||Watch what EM does behind the scenes. You can't just add a column to the
middle of the table (and not sure why you care where the column is anyway).
You have to drop the table and re-create it. An alternative is to create a
view with the columns defined in the correct order, but I still don't
understand why column order matters.
http://www.aspfaq.com/
(Reverse address to reply.)
"CurtM" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0801c44985$bd9fa1d0$a301280a@.phx.gbl...
> Can someone give me the syntax for adding a column into
> the middle of the table? I know how to do this in E.M,
> but I need to script this.