Use SQL to Check for a Field before using Alter Table

Wow, what a boring headline.

But this is a quick tip.

This is especially helpful when creating a DotNetNuke module and you want to run an Alter Table command on a table.  You don’t want the install script to fail because the field already exists.

Here’s how it’s done.

IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘Name_of_Table_to_Check‘ AND COLUMN_NAME = ‘Name_of_Field‘)
ALTER TABLE Name_of_Table_to_Check Add Name_of_Field int not null default 0

Change the bold parts to fit your needs and your good to go.

Happy coding!

Leave a Reply