[MS SQL]Cursor实例讲解1-对某个DB中所有的表加上一个字段

环境：SQL Server 2008

由于增加了新功能，需要对原来的Database中所有的表加一个相同的字段，用来表示某记录的某种状态。而这个database中的表非常多，一个个去加，就显得很笨了，是吧？查了半天，终于用SQL写了一个循环，将这个功能实现了。

1. 首先我们需要将某个表加一个字段

这个字段名为Lived，对应SQL语句如下：

 alter table tablename add [columnname] int

上面的tablename换成自己的表名，columnname换成自己的字段名即可。用中括号括上是为了避免和SQL Server的某些保留字冲突。

2. 在表中加上此字段后，我们需要将原有数据的Lived字段设为1

对应SQL 语句如下：

 update tablename set [columname]=1

3.如何取出某个数据库中所有表名呢?

当然仅限于我们自己创建的表,而不是系统表.用下面SQL语句就可以了.

 select name from sysobjects where xtype='u' 

感兴趣的朋友可以用select  * 来看看都能取出什么字段，对应内容是什么，这句话可是相当有用的哦。

 

4.接着就是SQL语句如何循环呢？

现在我们已经可以取到某个database中所有用户表 ，如何循环对每个表进行操作呢？

 declare tnames_cursor CURSOR 

 FOR 

    select name from sysobjects where xtype='u' 

 OPEN tnames_cursor

 DECLARE @table sysname

 FETCH NEXT FROM tnames_cursor INTO @table 

 while(@@FETCH_STATUS <> -1)

 begin 

    IF(@@FETCH_STATUS <> -2)

    BEGIN 

       --在这里写上你对每个表的操作  

    END 

 FETCH NEXT FROM tnames_cursor INTO @table 

 end 

 CLOSE tnames_cursor

 DEALLOCATE tnames_cursor

@@FETCH_STATUS
返回被   FETCH   语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
0     FETCH   语句成功(The FETCH statement was successful.)。  
-1   FETCH   语句失败或此行不在结果集中(The FETCH statement failed or the row was beyond the result set.)。  
-2                   被提取的行不存在(The row fetched is missing ).

5. 现在就剩最后一个问题了。如何在循环中执行SQL语句呢？

很简单，用exec就可以。如下：

 EXEC ('alter table ' + @table + ' add [Lived] int ')

 EXEC ('Update ' + @table + ' set [Lived]=1 ')

上面的[Lived]即为字段名。

 

到现在为止，就基本完成了我们想要的效果。

循环遍历某个database，对每个表进行添加[Lived]字段的操作，并将此表的所有记录的Lived初始化为1。完整代码如下：

 use databasename

 go 

 declare tnames_cursor CURSOR 

 FOR 

   select name from sysobjects where xtype='u' 

 OPEN tnames_cursor

 DECLARE @table sysname

 FETCH NEXT FROM tnames_cursor INTO @table 

 while(@@FETCH_STATUS <> -1)

 begin 

    IF(@@FETCH_STATUS <> -2)

    BEGIN 

       EXEC ('alter table ' + @table + ' add [Lived] int ')

       EXEC ('Update ' + @table + ' set [Lived]=1 ')

       print 'alter table ' + @table + ' add [Lived] int ' 

    END 

 FETCH NEXT FROM tnames_cursor INTO @table 

 end 

 CLOSE tnames_cursor

 DEALLOCATE tnames_cursor

更上一层楼
现在需求变了一点点。循环遍历某个Database，取出每个表的记录数，如果记录数为1，就打印出这个表名。
这里高级一点点的功能就是：执行select count(*)语句后，需要吧它记录在某个地方。下面先看完整代码：

 use databasename

 go 

 declare tnames_cursor CURSOR 

 FOR 

 select name from sysobjects where xtype='u' 

 OPEN tnames_cursor

 DECLARE @table sysname

 DECLARE @num int, @sqls nvarchar(4000)

 FETCH NEXT FROM tnames_cursor INTO @table 

 while(@@FETCH_STATUS <> -1)

 begin 

 IF(@@FETCH_STATUS <> -2)

 BEGIN 

 set @sqls = 'select @a=count(*) FROM ' + @table 

 exec sp_executesql @sqls, N'@a int output', @num output 

 if( @num = 1)

 BEGIN 

 print @table 

 END 

 END 

 FETCH NEXT FROM tnames_cursor INTO @table 

 end 

 CLOSE tnames_cursor

 DEALLOCATE tnames_cursor

大部分代码和上面是一样的。
这种情况下不能用EXEC()
要用 sp_executesql才行,因为变量的生存期只在sql动态语句中,出了就无效了.用sp_executesql可以解决这个问题.因为可以传参数,可是EXEC()不能够传参数。