updating the DB
-
Hi guys pls help,I want to add a new column to more than 500 tables in the database,pls help how can I do that.anyone who can provide me with a code??????????:-D cheerz
Good luck hehe :) You have to automate this somehow.. but don't panic.. I assume it's a SQL-database you are using. 1. you have to traverse all tables for your purpose.. tablenames is stored in the sysobject table. 2. for each table you have to fire ALTER table sql statement.. syntaxt is as following.. sample.... Examples A. Alter a table to add a new column This example adds a column that allows null values and has no values provided through a DEFAULT definition. Each row will have a NULL in the new column. ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL B. Alter a table to drop a column This example modifies a table to remove a column. ALTER TABLE doc_exb DROP COLUMN column_b C. Alter a table to add a column with a constraint This example adds a new column with a UNIQUE constraint. ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE
-
Good luck hehe :) You have to automate this somehow.. but don't panic.. I assume it's a SQL-database you are using. 1. you have to traverse all tables for your purpose.. tablenames is stored in the sysobject table. 2. for each table you have to fire ALTER table sql statement.. syntaxt is as following.. sample.... Examples A. Alter a table to add a new column This example adds a column that allows null values and has no values provided through a DEFAULT definition. Each row will have a NULL in the new column. ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL B. Alter a table to drop a column This example modifies a table to remove a column. ALTER TABLE doc_exb DROP COLUMN column_b C. Alter a table to add a column with a constraint This example adds a new column with a UNIQUE constraint. ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE
Does that mean that I have to fire ALTER table sql statement for each table manually?I actually want to autamate a text file looping through the table names in the sysobject table,also excluding the sys tables when altering,how can I do that? pls help. Thanx Guys!!
-
Does that mean that I have to fire ALTER table sql statement for each table manually?I actually want to autamate a text file looping through the table names in the sysobject table,also excluding the sys tables when altering,how can I do that? pls help. Thanx Guys!!
Sure you have to fire a Alter table statement... I could provide you with a code sample... but later on the day if you are patient. unless.. You have to read all tablenames from the sysobject-table with the xtype of 'U' and furthermore you have to deselect the tablename 'dtproperties' too, I gues the name is. -This will filter all the usertables.
-
Sure you have to fire a Alter table statement... I could provide you with a code sample... but later on the day if you are patient. unless.. You have to read all tablenames from the sysobject-table with the xtype of 'U' and furthermore you have to deselect the tablename 'dtproperties' too, I gues the name is. -This will filter all the usertables.
-
Sure I am patient. how can I generate a SQL script Automatically using a code? something like" Alter Tabe MyTable ADD Column Null Default getdate() pls help???
Here you go :) this will do it.. using System; namespace Tester { /// /// Summary description for Class2. /// public class Class1 { public Class1() { } public void GenerateColumns(System.Data.SqlClient.SqlConnection connection) { string selectstr = "SELECT name FROM sysobjects WHERE xtype ='U' AND name <> 'dtproperties' ORDER BY name";//select only user tables System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(selectstr, connection); cmd.Connection = connection; cmd.Connection.Open(); System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); while(dr.Read()) { this.AddColumn(dr.GetString(0), cmd.Connection.ConnectionString); } cmd.Connection.Close(); } private void AddColumn(string tableName, string connnectionStr) { //ALTER TABLE table ADD column VARCHAR(20) NULL string alterTableStr = string.Format("ALTER TABLE {0} ADD {1} {2} {3}", tableName, "YourColumnName", "datetime Default getdate()", "NULL"); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(alterTableStr, new System.Data.SqlClient.SqlConnection(connnectionStr)); try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); } catch(System.Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); } finally { cmd.Connection.Close(); } } } } -- modified at 14:02 Monday 5th September, 2005
-
Here you go :) this will do it.. using System; namespace Tester { /// /// Summary description for Class2. /// public class Class1 { public Class1() { } public void GenerateColumns(System.Data.SqlClient.SqlConnection connection) { string selectstr = "SELECT name FROM sysobjects WHERE xtype ='U' AND name <> 'dtproperties' ORDER BY name";//select only user tables System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(selectstr, connection); cmd.Connection = connection; cmd.Connection.Open(); System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); while(dr.Read()) { this.AddColumn(dr.GetString(0), cmd.Connection.ConnectionString); } cmd.Connection.Close(); } private void AddColumn(string tableName, string connnectionStr) { //ALTER TABLE table ADD column VARCHAR(20) NULL string alterTableStr = string.Format("ALTER TABLE {0} ADD {1} {2} {3}", tableName, "YourColumnName", "datetime Default getdate()", "NULL"); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(alterTableStr, new System.Data.SqlClient.SqlConnection(connnectionStr)); try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); } catch(System.Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); } finally { cmd.Connection.Close(); } } } } -- modified at 14:02 Monday 5th September, 2005