add column if it doesn't exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

add column if it doesn't exist

Author

Topic

dand

13 Posts

Posted - 2006-06-20 : 15:05:45

I found another thread with this solution but I can't get it to work. This is the code I'm running in QA (SS2000).

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')
BEGIN
ALTER TABLE dbo.tblCompanyContacts
ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
END

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 'dbo.tblCompanyContacts' is specified more than once.

TIA,

DanD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-20 : 15:10:08

You are trying to add MoreInformation3, however your IF NOT EXISTS says MoreInformation.

Tara Kizer
aka tduggan

dand

13 Posts

Posted - 2006-06-20 : 15:27:45

I was also using the wrong table_catalog. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-21 : 03:24:19

Other approach

If col_length('tableName','ColumnName') is NULL
--Add column

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-21 : 04:11:40

quote:
Originally posted by dand

I found another thread with this solution but I can't get it to work. This is the code I'm running in QA (SS2000).

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')
BEGIN
ALTER TABLE dbo.tblCompanyContacts
ADD MoreInformation3 BIT DEFAULT 0 NOT NULL
END

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 'dbo.tblCompanyContacts' is specified more than once.

TIA,

DanD