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())
{
dr.Dispose();
return true;
}
}
dr.Dispose();
tempcmd.CommandText = "Alter Table " + table_name + " Add " + field_name + " " + field_datatype;
tempcmd.ExecuteNonQuery();
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.