How to see if a table alreay has a column

Question

How to see if a table alreay has a column

river1

SSC-Insane

Points: 24309
More actions
June 17, 2014 at 4:23 am

#294445

Hi,
I want to run this command over all my databases that don't have this column:
alter table CONFIGURACAO add [USA_RFORIGEM] [bit] NULL
If it was a table I could do like this:
if exists (select * from sysobjects where name ='...')
begin
drop table...
end
create table .....
But, how can I prevent this script from running if the column already exists on the table. I only know how to do this for objects like tables, views, etc...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

You can use INFORMATION_SCHEMA.columns for the same purpose. Filter on the table and check if the column exists.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

river1 SSC-Insane Points: 24309 More actions · Answer 2

I have both realities:

Some databases are in SQL Server 2000 na others in SQL server 2005.

Can you show me the commands to do this?

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

river1 (6/17/2014)
Can you show me the commands to do this?

Koen already did.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rhythmk SSCertifiable Points: 7271 More actions · Answer 4

Koen Verbeeck (6/17/2014)
You can use INFORMATION_SCHEMA.columns for the same purpose. Filter on the table and check if the column exists.

It is applicable to both.

INFORMATION_SCHEMA VIEWS

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

rhythmk (6/17/2014)
Koen Verbeeck (6/17/2014)
You can use INFORMATION_SCHEMA.columns for the same purpose. Filter on the table and check if the column exists.
It is applicable to both.
INFORMATION_SCHEMA VIEWS

And that is why, ladies and gentlemen, we do not crosspost or duplicate post on these forums. 😀

http://www.sqlservercentral.com/Forums/Topic1582200-146-1.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rhythmk SSCertifiable Points: 7271 More actions · Answer 6

Koen Verbeeck (6/17/2014)
rhythmk (6/17/2014)
Koen Verbeeck (6/17/2014)
You can use INFORMATION_SCHEMA.columns for the same purpose. Filter on the table and check if the column exists.
It is applicable to both.
INFORMATION_SCHEMA VIEWS
And that is why, ladies and gentlemen, we do not crosspost or duplicate post on these forums. 😀
http://www.sqlservercentral.com/Forums/Topic1582200-146-1.aspx

My bad...did notice that it is a duplicate post 🙁

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂