Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

original article: http://www.cryer.co.uk/brian/sqlserver/howtoaddcolumnunlessexists.htm

 

To add a column to a SQL Server database table, checking first that the column does not already exist:

if not exists (select * from syscolumns
  where id=object_id('<
table_name>') and name='<column_name>')
    alter table <
table_name> add <column_name> <column_definition>

where: <table_name> is the name of the table, <column_name> is the name of the column and <column_definition> is the definition of the column used when creating the column.

For example:

 

if not exists (select * from syscolumns

  where id=object_id('INS_CORP_POLICY') and name='PROCESS_BUSINESS_UNIT_ID')

begin

    alter table INS_CORP_POLICY add PROCESS_BUSINESS_UNIT_ID int NULL

              print 'column added'

end

else

begin

              print 'column not added'

end

 

go

Posted on Thursday, March 1, 2007 6:34 AM SQL Server 2005 Tricks | Back to top


Comments on this post: Add a column to a table unless it already exists

# re: Add a column to a table unless it already exists
Requesting Gravatar...
You would be much better off querying the INFORMATION_SCHEMA.COLUMNS view instead of the system tables. Microsoft states that they will always keep the INFORMATION_SCHEMA views consistent from version to version.

You query would look something like this:

select * from INFORMATION_SCHEMA.COLUMNS
where Table_Name = 'INS_CORP_POLICY'
and Column_Name = 'PROCESS_BUSINESS_UNIT_ID'
Left by Justin on Mar 01, 2007 8:54 AM

# re: Add a column to a table unless it already exists
Requesting Gravatar...
Since recently I become greatly interested in it, but to my regret I seldom can find something worthy in the internet. Your site is an exception to the rule.
Left by writing service on Jun 22, 2010 7:20 AM

# re: Add a column to a table unless it already exists
Requesting Gravatar...
Next month I’ll have a presentation, and I am seeking for any info on this issue. Obviously you do have a lot of unique materials here on the site, as far as I can see. This artiocle is nice and I have referred it to many of my friends.
Left by drug test on Sep 30, 2010 7:46 AM

# re: Add a column to a table unless it already exists
Requesting Gravatar...
The policy is installed on "A" client. It is registered with the server and activated. When a user tries to checkin, the policy will be fail and the server can return a message (based on the code in the policy) telling the user how to install it. E.g., from an MSI on the Project Portal.
Left by icon editor on Nov 22, 2010 1:23 PM

# re: Add a column to a table unless it already exists
Requesting Gravatar...
Here we go. Great information. Thankyou
Left by Daniel Mathews on Nov 13, 2011 11:33 PM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net | Join free