Add one column in a table

I got ten database , and want to add one column in serveral tables.
However, I need to add that column in the fifth column .
Any easy script I can use 'thanks a lot"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:elCV8ntPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
NO. There isn't any easy script.
The table has to be dropped and recreated.
WHY to you need to insert the column at a specific position?|||Hi Agnes,
You can't insert column between existing columns. The physical order of
columns in rows in data files however doesn't matter.
The syntax for adding coulmnt to table is described in ALTER TABLE statement
in BOL
here's an example from BOL
ALTER TABLE doc_exf
ADD AddDate smalldatetime NULL
Since you are writing about order of columns I assume that you use * in your
statements. Don't do it. Select fields that you need and in order you need.
HTH
Peter|||That is not necessary because you can rearrange the column when you use the
select syntax.
i.e.
Select col1, col2, col3, col4, col12, col5 .....
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:elCV8ntPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
>|||> However, I need to add that column in the fifth column .
WHY? Are you doing SELECT * in your code? Create a view (and stop using
SELECT * anyway). Are you not bothering to list column names in your insert
statements? Please name the columns.

> Any easy script I can use '
No, T-SQL does not support this. What Enterprise Mangler does is dump the
data into a #temp table, re-create the table, and move the data back. So
not exactly something you'd want to do on a large data set, even if you
could justify it.
A|||Use this procedure :
IF EXISTS(SELECT [NAME] FROM dbo.sysobjects WHERE NAME='SP_ADDCOLUMN')
DROP PROCEDURE SP_ADDCOLUMN
go
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
GO
CREATE PROC sp_addcolumn
@.TableName sysname, --FieldName
@.FieldDefine nvarchar(1000), --COLUMNNAME,DATATYPE,[NULL OR NOT NULL]
'column_b VARCHAR(20) NULL'
@.Position int=-1 --POSITION,For example the value 1,so
need add to the first column,
AS
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@.TableNa
me),'IsUserTable'),0)=0
BEGIN
RAISERROR(N'Invalid "%s"',12,16,@.TableName)
return
END
--Add Field
DECLARE @.s nvarchar(4000)
SET @.s=N'ALTER TABLE '+QUOTENAME(@.TableName)+N' ADD '+@.FieldDefine
BEGIN TRAN
EXEC sp_executesql @.s
IF @.@.ERROR=0
BEGIN
DECLARE @.maxcolid int
SELECT @.Position=ISNULL(@.Position,0),@.maxcolid=
MAX(colid)
FROM syscolumns
WHERE id=OBJECT_ID(@.TableName)
IF @.Position>0 AND @.Position<@.maxcolid
UPDATE syscolumns SET
colid=CASE colid WHEN @.maxcolid THEN @.Position ELSE colid+1 END,
colorder=CASE colid WHEN @.maxcolid THEN @.Position ELSE colid+1 END
WHERE id=OBJECT_ID(@.TableName)
AND colid>=@.Position
COMMIT TRAN
END
ELSE
ROLLBACK TRAN
GO
--Close the System table write ability
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
--
After that run command:
EXECUTE SP_ADDCOLUMN 'B','column_b NVARCHAR(20) NULL',5
Here B is your table name, 5 is your new column position,column_b is your
column name
I have test it, it is ok.
Grant
"Agnes" wrote:

> I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
>
>|||Grant wrote:
> Use this procedure :
[snip]

> --
> After that run command:
> EXECUTE SP_ADDCOLUMN 'B','column_b NVARCHAR(20) NULL',5
> Here B is your table name, 5 is your new column position,column_b is your
> column name
> I have test it, it is ok.
>
DO NOT do it! This does not work for several reasons. In any case,
updating system tables is the surest way to corrupt your data
permanently.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--