更新数据库中所有表【插入新列】

1、通过SQL语句，循环数据库所有的表，分别插入新列“F_EditTime”

 

  1   use  DDDD
  2   /* Get Table Cursor */
  3   declare   @tablenname    varchar ( 50 );
  4   declare   @isExist   int ;
  5  
  6   /* 游标遍历数据库所有的用户表表名 */
  7   Declare  t_Table_cursor  Cursor   for
  8    SELECT  name  FROM  sys.sysobjects  where  Type = ' u ' ;
  9  
10   /* Open Cursor */
11   open  t_Table_cursor
12   /* Fetch  */
13   fetch   next   from  t_Table_cursor
14   into   @tablenname ;
15  
16   // 循环添加新列
17   while   @@Fetch_Status = 0
18    begin
19     if ( @tablenname   <>   '' )
20      begin
21        set   @isExist = 0 ;
22        select   @isExist = count ( * )  from  syscolumns  where  id = object_id ( @tablenname )  and  name = ' F_EditTime ' ; 
23        print   @isExist
24        if ( cast ( @isExist   as   int )  =   0 )
25         begin
26          print   ' in  ' + @tablenname
27          exec ( ' alter table  ' + @tablenname + '  add F_EditTime datetime default getdate()  ' );
28          exec ( ' update  ' + @tablenname + '  set  F_EditTime=getdate() ' );
29         end
30      end
31     Fetch   next   from  t_Table_cursor 
32        into   @tablenname ;
33    end
34   /* close and Release */
35   close  t_Table_cursor;
36   DEALLOCATE  t_Table_cursor;
37  
38  微软权威的存储过程
39     DECLARE     @TableName     varchar ( 255 )  
40     DECLARE     @ExeSQL     varchar ( 4000 )    
41     DECLARE    Table_Cursor    CURSOR     FOR     SELECT     [ name ]     FROM    sysobjects    WHERE    xtype = ' U '   
42     OPEN    Table_Cursor  
43     FETCH     NEXT     FROM      Table_Cursor    INTO     @TableName   
44     WHILE ( @@FETCH_STATUS = 0 )  
45     BEGIN   
46       PRINT     @TableName   
47       /* SELECT   @ExeSQL='DBCC   CHECKTABLE ('''+@TableName+''')'    */
48       exec ( ' alter table  ' + @TableName + '  add F_EditTime datetime default getdate()  ' );
49       EXEC ( @EXESQL )  
50     FETCH     NEXT     FROM      Table_Cursor    INTO     @TableName   
51     END   
52     CLOSE    Table_Cursor  
53     DEALLOCATE    Table_Cursor  
54     GO

2、当然这个也可以通过Application实现
   具体如下

1      protected void Button2_Click(object sender, EventArgs e)
  2      {
  3          ArrayList namesList = getAllNames();
  4           for  ( int  i  =   0 ; i  <  namesList. Count ; i ++ )
  5          {
  6               if  (!isExit(namesList [ i ] .ToString()))
  7              {
  8                  updateTable(namesList [ i ] .ToString());     
  9              }   
10          }
11      }
12       // 执行插入列操作
13       public  bool updateTable(string tableName)
14      {
15          bool success  =  false ;
16          SqlConnection con  =  DB.CreateServerCon();
17          string strSql  =  "  alter   table  " + tableName + "   add  F_EditTime  datetime   default   getdate ()";      
18          string updateSql = " update  "  +  tableName  +  "  set  F_EditTime  =   getdate ()";
19          SqlCommand cmd  =  new SqlCommand(strSql, con);
20          SqlCommand updateCmd  =  new SqlCommand(updateSql, con);
21          try
22          {
23              con. Open ();
24              Convert .ToInt32(cmd.ExecuteNonQuery()); 
25              Convert .ToInt32(updateCmd.ExecuteNonQuery());      
26             success  =  true;     
27          }
28          catch
29          { }
30          finally
31          {
32              con. Close ();
33          }
34           return  success;
35      }
36       ///   < summary >
37       ///  判断在表中字段名是否存在
38       ///   </ summary >
39       ///   < param name = "tableName" > 表名 </ param >
40       ///   < returns ></ returns >
41       public  bool isExit(string tableName)
42      {
43          bool success  =  false ;
44          SqlConnection con  =  DB.CreateServerCon();       
45          string strSql  =  " select   count ( * )  from  syscolumns  where  id = object_id ( ' "+tableName+" ' )  and  name = ' F_EditTime ' ";
46          SqlCommand cmd  =  new SqlCommand(strSql, con);
47          try
48          {
49              con. Open ();
50               int   count   =   Convert .ToInt32(cmd.ExecuteScalar());
51               if  ( count   >   0 )
52              {
53                  success  =  true;
54              }
55          }
56          catch
57          { }
58          finally
59          {
60              con. Close ();
61          }
62           return  success;
63      }
64       ///   < summary >
65       ///  得到数据库中所有的用户表
66       ///   </ summary >
67       ///   < returns ></ returns >
68       public  ArrayList getAllNames()
69      {
70          ArrayList namesList = new ArrayList();
71          SqlConnection con  =  DB.CreateServerCon();
72          string strSql  =  " SELECT  name  FROM  sys.sysobjects  WHERE  type = ' U '   order   by  name";       
73          SqlCommand cmd  =  new SqlCommand(strSql, con);
74          try
75          {
76              con. Open ();
77              SqlDataReader sdr  =  cmd.ExecuteReader();
78               while (sdr. Read ())
79              {
80                  namesList. Add (sdr [ 0 ] .ToString());
81              }
82          }
83          catch
84          { }
85          finally
86          {
87              con. Close ();
88          }
89           return  namesList;
90      }

转载于:https://www.cnblogs.com/spark_wu/archive/2009/07/28/1532911.html