SQL Field creation Script for fields that may already exist

[nameunknown]

Hi everyone,

we have constructed a content management system that gets updated quite frequently and is used by many clients for their day to day opperations for their websites.

We have decided to build a section of the CMS with additional modules that we will host on our cloud and these applications may update quite frequently.

Because of this we have quite a number of individual databases created for each website that has been deployed onto our network.

As we will be adding new fields to the databases as enhancements come into effect periodically, i am concerned that this will delay us quite alot in installing the updates.

What i would like to know is...

Is there away to apply conditional logic in an SQL query for modifying the actual databases individually.

example.

we have a table called members.

We would like to add a series of fields to the database

username
companyname
wifesname

but as the table might be slightly different and may not contain these fields, and adding them will cause an error.

Is there an easy way to detect if a field exists and if not, simply create the required fields on the fly ?

we currently use MSSQL 2005 and Coldfusion, but i am more concerned about the SQL portion than anything else

thanks

[BitBlt]

You can test for the existence of columns in a specific table using the INFORMATION_SCHEMA.COLUMNS system view, and wrap it inside of an "exists" construct like so:

if not exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'members' and COLUMN_NAME = 'username')
begin
	alter table dbo.members add username varchar(10) null
end

You can, of course, replace the literals with ColdFusion variables as appropriate.

Note, however, that this will only execute for logins that have suitable permissions. If you want everyone to be able to do this on the fly, you have to grant elevated SQL permissions. That may not be prudent, so you may want to consider carefully before implementing something like this. If you're using a service account in your ODBC (or OLEDB) connection to access the database, it might be okay...but again, consider carefully.

Good luck!