Adding New Field In Database

By Kunal Vaishya Oct 26 2012
Interesting thing in code to add one field in table once the table is already in the database.
    • Like
    • Love It
    • Awesome
    • Interesting
    • It's Okay
    • Thumbs Down
  • 1.6k
  • 0
In most of the product we have use script for updating Tables, views, procedure and functions, I found one very interesting thing in my code when I add one Field in Table once the table is already in the database
Here is logic for adding one column
tempcmd.CommandText = "SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + table_name + "'";
dr = tempcmd.ExecuteReader();
while (dr.Read())
    if (dr["COLUMN_NAME"].ToString().ToUpper() == field_name.ToUpper())
      return true;
tempcmd.CommandText = "Alter Table " + table_name + " Add " + field_name + " " + field_datatype;
return true;
Instead of using this Query we have to add one more filteration on Column_Name so that we don't have to Loop.
Query = SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tblname' and Column_Name='abc'
Secong thing we can increase the Speed of execution.
Please reply if any one have any problem in this Condition.