Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi All

Can you pls help me.

I have to add some columns to the existing table.


I have to write a script if column does not exist then add otherwise alter that table.
Can anyone pls give me an example.


Thanks

Suman
Posted

Try:
SQL
IF NOT EXISTS
  ( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'MyTable' AND column_name = 'MyNewColumn'
  )
  BEGIN
    ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL default 1;
  END
 
Share this answer
 
Comments
babli3 8-May-14 10:04am    
Hi Thanks
but in the same script i need to even add if it exists then alter the column .
How Do I do that? Thanks
OriginalGriff 8-May-14 10:18am    
Sorry?
You can't add a column that exists...
Maciej Los 8-May-14 15:43pm    
+5
First, as noted above, you can not have two columns with the same name in the same table. Since you must add the column and alter it, then simply enhance the script above:

SQL
IF NOT EXISTS
  ( SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'MyTable' AND column_name = 'MyNewColumn'
  )
  BEGIN
    ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL default 1;
  END
ELSE
  BEGIN
   -- Do whatever needs to be done for adding an existing columns
  END
 
Share this answer
 
Comments
Maciej Los 8-May-14 15:43pm    
+5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900