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:
>