Update all tables in the database "Insert new column"

1, through the SQL statement, loop all the tables of the database, insert the new column "F_edittime", respectively

1 UseDDDD
2/*Get Table Cursor*/
3Declare@tablennamevarchar( -);
4Declare @isExistint;
5
6/*cursors traverse database for all user table table names*/
7DeclareT_table_cursorCursor for
8SELECTName fromSys.sysobjectswhereType='u';
9
Ten/*Open Cursor*/
OneOpenT_table_cursor
A/*Fetch*/
-FetchNext fromT_table_cursor
- into@tablenname;
the
-//Loop to add a new column
- while@ @Fetch_Status=0
-begin
+if(@tablenname<>"')
-begin
+Set@isExist=0;
ASelect@isExist=Count(*) fromsyscolumnswhereId=object_id(@tablenname) andName='F_edittime';
atPrint @isExist
-if(cast(@isExist asint)=0)
-begin
-Print'inch'+@tablenname
- exec('ALTER TABLE'+@tablenname+'Add f_edittime datetime default GETDATE ()');
-exec('Update'+@tablenname+'set F_edittime=getdate ()');
inEnd
-End
toFetchNext fromT_table_cursor
+ into@tablenname;
-End
the/*Close and Release*/
* CloseT_table_cursor;
$deallocateT_table_cursor;
Panax Notoginseng
-Microsoft's authoritative stored procedures
theDECLARE@TableNamevarchar(255)
+DECLARE@ExeSQLvarchar(4000)
ADECLARETable_cursorCURSOR forSELECT[name] fromsysobjectsWHEREXtype='U'
theOPENTable_cursor
+ FETCHNEXT fromTable_cursor into@TableName
- while(@ @FETCH_STATUS=0)
$BEGIN
$PRINT@TableName
-/*SELECT @ExeSQL = ' DBCC checktable (' [email protected]+ ') '*/
-exec('ALTER TABLE'+@TableName+'Add f_edittime datetime default GETDATE ()');
theEXEC(@EXESQL)
-FETCHNEXT fromTable_cursor into@TableName
WuyiEND
theCLOSETable_cursor
-deallocateTable_cursor
WuGO

2, of course, this can also be achieved through application
Specific as follows:

1protectedvoidButton2_Click (Objectsender, EventArgs e)
2{
3ArrayList Nameslist=getallnames ();
4 for(inti = 0; i < Nameslist.count; i++)
5{
6if(!isexit (nameslist[i). ToString ()))
7{
8UpdateTable (Nameslist[i]. ToString ());
9}
Ten}
One}
A//Perform an Insert column operation
- PublicBOOLUpdateTable (stringTableName)
-{
theBOOLSuccess =false;
-SqlConnection con = DB. Createservercon ();
-stringstrSQL = "ALTER TABLE" +tablename+ "Add f_edittime datetime default getdate ()";
-stringUpdatesql= "Update" + TableName + "Set f_edittime = GetDate ()";
+SqlCommand cmd =NewSqlCommand (strSQL, con);
-SqlCommand Updatecmd =NewSqlCommand (Updatesql, con);
+Try
A{
atCon. Open ();
-Convert.ToInt32 (cmd. ExecuteNonQuery ());
-Convert.ToInt32 (Updatecmd.executenonquery ());
-Success =true;
-}
-Catch
in{ }
-finally
to{
+Con. Close ();
-}
thereturnSuccess
*}
$/// <summary>
Panax Notoginseng    ///determine if the field name exists in the table
-    /// </summary>
the    /// <param name= "TableName" >Table name</param>
+    /// <returns></returns>
A PublicBOOLIsexit (stringTableName)
the{
+ BOOLSuccess =false;
-SqlConnection con = DB. Createservercon ();
$stringstrSQL = "SELECT count (*) from syscolumns where id=object_id ('" +tablename+ "') and Name= ' F_edittime '";
$SqlCommand cmd =NewSqlCommand (strSQL, con);
- Try
-{
theCon. Open ();
-intCount = Convert.ToInt32 (cmd. ExecuteScalar ());
Wuyiif(Count > 0)
the{
-Success =true;
Wu}
-}
AboutCatch
${ }
-finally
-{
-Con. Close ();
A}
+returnSuccess
the}
-/// <summary>
$    ///get all the user tables in the database
the     /// </summary>
the    /// <returns></returns>
the PublicArrayList Getallnames ()
the{
-ArrayList nameslist=NewArrayList ();
inSqlConnection con = DB. Createservercon ();
thestringstrSQL = "SELECT name from Sys.sysobjects WHERE type= ' U ' ORDER by name";
theSqlCommand cmd =NewSqlCommand (strSQL, con);
AboutTry
the{
theCon. Open ();
theSqlDataReader SDR = cmd. ExecuteReader ();
+ while(SDR). Read ())
-{
theNameslist.add (Sdr[0]. ToString ());
Bayi}
the}
theCatch
-{ }
-finally
the{
theCon. Close ();
the}
the returnNamesList;
-}