Adding a new column in a certain position....

Hi,
I am writing some updates scripts for a database and need to add a new
column at a specific position in the table. I am using the 'ALTER TABLE'
command but this just tags it on the end. Is there any way of doing (via
TSQL) this without rebuilding the table?
Thanks in advance,
Stuno, there isn't. but there isn't a good reason for doing this in the first
place - at least not the one i know of. why is the order of columns in base
table important to you?
dean
"Stu" <s.lock@.cergis.com> wrote in message
news:uL%230ep9KGHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>|||Stu skrev:

> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
This is a common question, and the anwer is 'no' :)
Search for it on Google for example, then you'll learn that you should
not really care about in which order the columns are in a table.
/impslayer, aka Birger Johansson|||Stu,
There is no good reason (that I have come across) to need the columns in any
particular order (except, perhaps, for clarity at some stage), but
operationally, they should make no difference whatsoever.
As a side point, they may make a difference should you use the SELECT * FROM
tblMyTable, but then again, that is not good practise to do, I always state
my Selected column names, for both clarity and to ensure that should a schem
a
change occur in the underlaying table (e.g. a column added) then the same,
correct, columns are returned each time.
Hope this assists,
Tony
"Stu" wrote:

> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>|||http://www.aspfaq.com/2528
"Stu" <s.lock@.cergis.com> wrote in message
news:uL%230ep9KGHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>

adding a field

What's the SQL for adding a field to a table? And can you direct me to the
documentation. Is it an ALTER TABLE?you got it
alter table is it.
google alter table
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:A45A259E-D86C-4B19-B923-A39BFCE0255E@.microsoft.com...
> What's the SQL for adding a field to a table? And can you direct me to
> the
> documentation. Is it an ALTER TABLE?|||Alter table add <columnname> <datatype>
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:A45A259E-D86C-4B19-B923-A39BFCE0255E@.microsoft.com...
> What's the SQL for adding a field to a table? And can you direct me to
> the
> documentation. Is it an ALTER TABLE?|||Yes it's the ALTER TABLE statement.
If you look in BOL it's under ALTER TABLE, but the code is :
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
Adam J Warne, MCDBA
"Les Stockton" wrote:

> What's the SQL for adding a field to a table? And can you direct me to th
e
> documentation. Is it an ALTER TABLE?|||Yes, it's:
ALTER TABLE table_name ADD col_name col_type col_nullability;
For example,
ALTER TABLE T1 ADD col1 INT NOT NULL DEFAULT(0);
SQL Server's Books Online has info about this under ALTER TABLE.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:A45A259E-D86C-4B19-B923-A39BFCE0255E@.microsoft.com...
> What's the SQL for adding a field to a table? And can you direct me to
> the
> documentation. Is it an ALTER TABLE?

adding a column then using it in a script

Hi,
I would like to alter a table in a sql server database then update the column with data in the same script. However, the database does not recognize the database column if I create it within the script. Is there a way to refresh this within the script so that I can run this in one procedure? If I create the table in one script then update in a second script it will work. Thanks.

LauraReally?

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))

INSERT INTO myTable99(Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'

SELECT * FROM myTable99

ALTER TABLE myTable99 ADD Col3 int

SELECT * FROM myTable99

UPDATE myTable99 SET Col3 = 1

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Why not post your code?|||I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.|||Originally posted by joejcheng
I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.

Did you cut and paste my code?|||I am talking about what Laura might have done. My guess is she cut and paste the script generated by SQL and put in an insert or update statement between BEGIN TRANS and COMMIT.|||Commit! Duh! Sometimes you overlook the easy stuff. Ok Thank you so much.sql

Add new column?

How do you add a new column in a table between two existing columns (like when you add columns with enterprise manager) using alter table?
/y0d4Is this a slow forum or is the topic to trivial for people to open it?

/y0d4|||An ATLER TABLE statement only adds the column to the end and since this is a relational database there is no problem where the column appears. However if you wish to add the column in a partical position then:

Example

CREATE TblA
(
col1 int,
col3 int,
col4 int
)

exec sp_rename Tb1A, OldTb1A
go

CREATE TblA
(
col1 int,
col2 int,
col3 int,
col4 int
)
GO
INSERT Tb1A (col1,col2,col3 ,col4 )
SELECT col1, 0,col3 ,col4
FROM OldTb1A
GO

DROP TABLE OldTb1A
GO

Do get a better idea of handling IDENTITY and so forth columns turn Profiler on and go into Enterprise Manager under the design Table option and insert a column into the middle of table and Save. You will see all the transactions that Microsoft does to perform this in Profiler.

Add new column to a replicated table..

Hi,
I need to add a column to a table that is being replicated.
Can I just do the ALTER TABLE ADD xxxx bit then make a call out to
sp_replAddColumn?
When I do so, the sp fails saying the column exists. The manual says that it
adds the column to the publications, but does not suggest it does the
physical add tot the table..
Thanks in advance,
SPGYou use sp_replAddColumn INSTEAD of using alter table add column... the Sp
adds the column to the table, and fixes replication... since you manually
added the column the sp fails.... drop the column and use the SP only.
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
"SPG" <steve.nospoo.goodsell@.nopooblueyonder.co.nopoo.uk> wrote in message
news:r9IQd.16027$8B3.14345@.text.news.blueyonder.co.uk...
> Hi,
> I need to add a column to a table that is being replicated.
> Can I just do the ALTER TABLE ADD xxxx bit then make a call out to
> sp_replAddColumn?
> When I do so, the sp fails saying the column exists. The manual says that
> it adds the column to the publications, but does not suggest it does the
> physical add tot the table..
> Thanks in advance,
> SPG
>|||Thanks for that..
I sort of half guessed that when the sp failed, but the docs are not too
clear on this..
Steve
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23oEXG8CFFHA.2568@.TK2MSFTNGP10.phx.gbl...
> You use sp_replAddColumn INSTEAD of using alter table add column... the
> Sp adds the column to the table, and fixes replication... since you
> manually added the column the sp fails.... drop the column and use the SP
> only.
> --
> 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
> "SPG" <steve.nospoo.goodsell@.nopooblueyonder.co.nopoo.uk> wrote in message
> news:r9IQd.16027$8B3.14345@.text.news.blueyonder.co.uk...
>

ADD IDENTITY PROPERTY WHEN THERE IS ALREADY DATA

Dear all,
Keep in mind the structure of the following table I would need alter ID
field and add an IDENTITY property but when data are already loaded.
The source table begin from 4200 as value in the first row and if before of
that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from 1
.
And so that it's a disaster.
Let me know how would I work out this issue.
CREATE TABLE [dbo].[VIA_DebugInfo] (
[Id] [int] NOT NULL ,
[Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
This one doesn't work, I haven't idea!
SET IDENTITY_INSERT via_debuginfo OFFCheck out the seed in the BOL
this is what I have done
CREATE TABLE [Claim] (
[ClaimID] [int] IDENTITY (15621, 1) NOT NULL ,
...
...
...
I would assume that you would ALTER the table add the new column, setting
its seed to the number you want
RObert
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:4DEDB4D8-BDA2-4985-BE46-287F1FADB537@.microsoft.com...
> Dear all,
> Keep in mind the structure of the following table I would need alter ID
> field and add an IDENTITY property but when data are already loaded.
> The source table begin from 4200 as value in the first row and if before
of
> that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from
1.
> And so that it's a disaster.
> Let me know how would I work out this issue.
> CREATE TABLE [dbo].[VIA_DebugInfo] (
> [Id] [int] NOT NULL ,
> [Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
> CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> GO
> This one doesn't work, I haven't idea!
> SET IDENTITY_INSERT via_debuginfo OFF|||Enric donotspam wrote:
> Dear all,
> Keep in mind the structure of the following table I would need alter ID
> field and add an IDENTITY property but when data are already loaded.
> The source table begin from 4200 as value in the first row and if before o
f
> that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from
1.
> And so that it's a disaster.
> Let me know how would I work out this issue.
> CREATE TABLE [dbo].[VIA_DebugInfo] (
> [Id] [int] NOT NULL ,
> [Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
> CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> GO
> This one doesn't work, I haven't idea!
> SET IDENTITY_INSERT via_debuginfo OFF
You can't make an existing column an IDENTITY.
You could add a new IDENTITY column. Using the IDENTITY seed argument
you can start the sequence at 4200 but you can't control which row gets
which value. Failing that you have to create a new table. Wisest option
is to avoid ascribing any business meaning to an IDENTITY column. If
you stick to that principle you won't have this problem.
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
--|||tHANKS TO BOTH FOR YOUR HELP
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Enric" wrote:

> Dear all,
> Keep in mind the structure of the following table I would need alter ID
> field and add an IDENTITY property but when data are already loaded.
> The source table begin from 4200 as value in the first row and if before o
f
> that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from
1.
> And so that it's a disaster.
> Let me know how would I work out this issue.
> CREATE TABLE [dbo].[VIA_DebugInfo] (
> [Id] [int] NOT NULL ,
> [Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
> CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> GO
> This one doesn't work, I haven't idea!
> SET IDENTITY_INSERT via_debuginfo OFF

add column if it doesn't exist

Using SS2000. In an alter table statement,is there a way to check if a colum
n
exists before you try to add it?
Thanks,
--
Dan D.IF COLUMNPROPERTY
(
OBJECT_ID('dbo.table_name'),
'column_name',
'AllowsNull'
) IS NULL
BEGIN
ALTER TABLE [dbo].[table_name] ADD [column_name] [data type];
END
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:4C8AEE4D-9CAF-4DBC-B118-370769CD2F01@.microsoft.com...
> Using SS2000. In an alter table statement,is there a way to check if a
> column
> exists before you try to add it?
> Thanks,
> --
> Dan D.|||Try checking existence on view INFORMATION_SCHEMA.COLUMNS
Example:
use northwind
go
if not exists (
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'orders' and column_name = 'c1'
)
alter table dbo.orders
add c1 int not null constraint df_c1 default(0) with values
go
select COLUMN_NAME, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'orders'
order by ORDINAL_POSITION
go
alter table orders
drop constraint df_c1
go
alter table orders
drop column c1
go
AMB
"Dan D." wrote:

> Using SS2000. In an alter table statement,is there a way to check if a col
umn
> exists before you try to add it?
> Thanks,
> --
> Dan D.|||Dan D. wrote:
> Using SS2000. In an alter table statement,is there a way to check if a col
umn
> exists before you try to add it?
> Thanks,
In your database:
IF EXISTS(SELECT 'x' FROM syscolumns WHERE id = <id of your table> )
Or combine with a join to sysobjects looking for your table name|||You can query INFORMATION_SCHEMA.COLUMNS
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:4C8AEE4D-9CAF-4DBC-B118-370769CD2F01@.microsoft.com...
> Using SS2000. In an alter table statement,is there a way to check if a
> column
> exists before you try to add it?
> Thanks,
> --
> Dan D.|||You first example is what I tried
IF NOT EXISTS (select * from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'SMCLMS_Dev' and TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tblCompanyContacts' and COLUMN_NAME = 'MoreInformation')
ALTER TABLE tblCompanyContacts
ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
but I get this error:
Server: Msg 2705, Level 16, State 4, Line 4
Column names in each table must be unique. Column name 'MoreInformation3' in
table 'tblCompanyContacts' is specified more than once.
Dan D.
"Alejandro Mesa" wrote:
> Try checking existence on view INFORMATION_SCHEMA.COLUMNS
> Example:
> use northwind
> go
> if not exists (
> select *
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'orders' and column_name = 'c1
'
> )
> alter table dbo.orders
> add c1 int not null constraint df_c1 default(0) with values
> go
> select COLUMN_NAME, DATA_TYPE
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'orders'
> order by ORDINAL_POSITION
> go
> alter table orders
> drop constraint df_c1
> go
> alter table orders
> drop column c1
> go
>
> AMB
>
> "Dan D." wrote:
>|||You're checking if 'MoreInformation' exists and then adding
'MoreInformation3'
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:263AA1A3-A454-4741-BF8F-9285116952BF@.microsoft.com...
> You first example is what I tried
> IF NOT EXISTS (select * from INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_CATALOG = 'SMCLMS_Dev' and TABLE_SCHEMA = 'dbo'
> AND TABLE_NAME = 'tblCompanyContacts' and COLUMN_NAME = 'MoreInformation')
> ALTER TABLE tblCompanyContacts
> ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
> but I get this error:
> Server: Msg 2705, Level 16, State 4, Line 4
> Column names in each table must be unique. Column name 'MoreInformation3'
> in
> table 'tblCompanyContacts' is specified more than once.
> --
> Dan D.
>
> "Alejandro Mesa" wrote:
>|||Good catch!!!
AMB
"Aaron Bertrand [SQL Server MVP]" wrote:

> You're checking if 'MoreInformation' exists and then adding
> 'MoreInformation3'
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:263AA1A3-A454-4741-BF8F-9285116952BF@.microsoft.com...
>
>|||Thanks. I ran this:
IF NOT EXISTS (select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG
= 'SMCLMS_Dev' and TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tblCompanyContacts' and COLUMN_NAME = 'MoreInformation3')
BEGIN
ALTER TABLE dbo.tblCompanyContacts
ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
END
and I get the same error as before -
Server: Msg 2705, Level 16, State 4, Line 4
Column names in each table must be unique. Column name 'MoreInformation3' in
table 'dbo.tblCompanyContacts' is specified more than once.
Dan D.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You're checking if 'MoreInformation' exists and then adding
> 'MoreInformation3'
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:263AA1A3-A454-4741-BF8F-9285116952BF@.microsoft.com...
>
>|||Dan,
Are you running the script on database [SMCLMS_Dev]?
use SMCLMS_Dev
go
IF NOT EXISTS ...
GO
AMB
"Dan D." wrote:
> Thanks. I ran this:
> IF NOT EXISTS (select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALO
G
> = 'SMCLMS_Dev' and TABLE_SCHEMA = 'dbo'
> AND TABLE_NAME = 'tblCompanyContacts' and COLUMN_NAME = 'MoreInformation3'
)
> BEGIN
> ALTER TABLE dbo.tblCompanyContacts
> ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
> END
> and I get the same error as before -
> Server: Msg 2705, Level 16, State 4, Line 4
> Column names in each table must be unique. Column name 'MoreInformation3'
in
> table 'dbo.tblCompanyContacts' is specified more than once.
> --
> Dan D.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Add a default value to an existing column

How can I add a default value to an existing column via T-SQL? I tried the
following but not successful:
Alter Table MyTbl Alter Column MyCol Int Default 0.8
TIANote that it will not update previously stored null values
Madhivanan|||The statement fails even if the table is empty (no record). I get the error
message:
"Incorrect syntax near the keyword 'Default'."
The statement I tried: Alter Table MyTbl Alter Column MyCol Int Default 0.8
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1111053136.539506.125770@.z14g2000cwz.googlegroups.com...
> Note that it will not update previously stored null values
> Madhivanan
>|||Hi
create table #test
(
col decimal(18,3)
)
alter table #test add constraint my_def default 18.3 for col
"krygim" <krygim@.hotmail.com> wrote in message
news:eS$fAhtKFHA.436@.TK2MSFTNGP09.phx.gbl...
> The statement fails even if the table is empty (no record). I get the
error
> message:
> "Incorrect syntax near the keyword 'Default'."
> The statement I tried: Alter Table MyTbl Alter Column MyCol Int Default
0.8
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1111053136.539506.125770@.z14g2000cwz.googlegroups.com...
>|||It works. Thanks Uri.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kQoOytKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Hi
> create table #test
> (
> col decimal(18,3)
> )
> alter table #test add constraint my_def default 18.3 for col
>
>
>
> "krygim" <krygim@.hotmail.com> wrote in message
> news:eS$fAhtKFHA.436@.TK2MSFTNGP09.phx.gbl...
> error
> 0.8
>

Add a column to table from stored proc

Hello all,

I want to alter a table in a stored proc to add a column dynamically. I am passing the name of the new column to the stored proc param. Does anyone know the syntax for this? Here is the code:

CREATE PROCEDURE procAddNewOptionType

-- new option type parameter

@.OptionName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert new option type into list of types

INSERTINTO dbo.tblClientOptionList(strOptionName)VALUES(@.OptionName);

-- alter client options table to add new column passed to proc as @.OptionName

ALTERTABLE dbo.tblClientOptions ADD @.OptionName BIT;

RETURN 1

END

Thx,

Jason

Hi jason,

You'd have to execute the ALTER TABLE statements dynamically since your column depends on the OptionName being a variable.

declare @.sql nvarchar(100)

SET @.sql = 'alter table dbo.tblClientOptions add ' + @.optionName + ' BIT'
EXEC sp_executesql @.sql

Although, you'd have to take note possible permissions priviledge given by your DBA(ie. they might revoke ALTER TABLE permission)

hth|||

Thank you very much. That works perfectly!

Jason

Add a column to table from stored proc

Hello all,

I want to alter a table in a stored proc to add a column dynamically. I am passing the name of the new column to the stored proc param. Does anyone know the syntax for this? Here is the code:

CREATE PROCEDURE procAddNewOptionType

-- new option type parameter

@.OptionName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert new option type into list of types

INSERTINTO dbo.tblClientOptionList(strOptionName)VALUES(@.OptionName);

-- alter client options table to add new column passed to proc as @.OptionName

ALTERTABLE dbo.tblClientOptions ADD @.OptionName BIT;

RETURN 1

END

Thx,

Jason

Hi jason,

You'd have to execute the ALTER TABLE statements dynamically since your column depends on the OptionName being a variable.

declare @.sql nvarchar(100)

SET @.sql = 'alter table dbo.tblClientOptions add ' + @.optionName + ' BIT'
EXEC sp_executesql @.sql

Although, you'd have to take note possible permissions priviledge given by your DBA(ie. they might revoke ALTER TABLE permission)

hth|||

Thank you very much. That works perfectly!

Jason