Checking if fields exist - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Checking if fields exist

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-03-14 : 08:00:34

Hello all.

I am going to be writting a script to add columns to a given table. Can anyone advise me how i check if the coulmn exists in a table before i modify it?

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 08:04:04

select * from information_schema.columns

Peter Larsson
Helsingborg, Sweden

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-03-14 : 08:06:01

hi, thanks for the reply. However i'm not really sure this ties in with my question.

Say i'm looking to check if a field called ADDRESS1 already exists in the EMPLOYEE_TABLE and if it doesnt then create it.

Cheers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 08:11:19

PLEASE TRY TO RUN THE SUGGESTED QUERY BEFORE YOU DECIDE TO DISS IT!

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'address1' AND TABLE_NAME = 'EMPLOYEE_TABLE)
-- Do stuff when the column exists
ELSE
-- Do stuff when the column do not exists

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 08:12:24

[code]If not Exists(Select * from information_schema.columns where table_name = 'EMPLOYEE_TABLE' and column_name = ADDRESS1')
-- add column
Alter table
Add ...
GO[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 08:13:15

If you would have run the suggested query, you would have figured out what to do next.

Peter Larsson
Helsingborg, Sweden

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-03-14 : 08:16:45

Woooah......Peso.....relax.

I wasnt in any way disrespecting what you'd suggested....quite the opposite.

Fair enough in saying i could have figured it out if i'd run the quesry......however I am posting in a 'New to SQL' forum. This to me should indicate that i may not be the most knowledgable or confident in this area and running script which i dont not understand may not be a good idea.

Anyway, i am greatfull for the help and advice you provided.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 08:28:03

You're welcome.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources