Using conditionals before adding columns to a table in SQL server

Using conditionals before adding columns to a table in SQL server

Working with databases is not as easy as it looks like, specially if you are working with production databases. When a database is production you need to be very careful with the changes you make, adding, removing tables, columns, changing data, etc. Once a database is in production you use incremental scripts in order to push new updates into it.

These incremental scripts need to handle everything and they do not have to have any errors and must be able to run multiple times if it is needed. One thing I keep seeing in lots of incremental scripts are the lack of verification before they are executed. The other day I asked one of my developers to add one column to a table and also to write the respective incremental script and UI controller.

This is what I got:

ALTER TABLE [table] ADD [column_name] VARCHAR(10) DEFAULT [defaultvalue];



There are a couple of things i do not like about this script but one of them was the lack of verification. If you execute this one time it will work but if you try to execute it more than once it will fail because the column is already there and you are trying to add it again.

In SQL server there is a schema which stores all the information about your tables, schema, columns everything and in order to verify if something is already in your DB, you can query that schema and then based on the results you can execute your scripts.

By doing that I ended up with this new script:

IF NOT EXISTS ( SELECT * FROM Information_Schema.ColumnsWHERE table_name=ois_incidentAND column_name = EntryMethod)BEGIN
   ALTER TABLE oishealth_dev.OIS_Incident
   ADD EntryMethod VARCHAR(10) DEFAULT ZPICEND;

Now this script can be executed multiple times and you can be sure it will never fail .

happy coding :)

download file now