Who am I?

一直想用 drop table 删除多个表，以前捣鼓一阵，没有成功。别人给了个用游标的，我一听游标就头大了。大学时学的东西，现在只知道有个这么个名词了。干什么的都忘记了。

直到昨天，翻开大学时的笔记，弄明白的了游标的含义。还有个例子，就在MSSql中敲了起来，可是mssql的和Oracle的不一样啊。最后在同事的权利配合下，终于搞出来了。

//选择数据库

use DataName

//声明一个游标
declare @name varchar(128);

//从sysobjects 表中查找到符合条件的要删除的表

declare  cursor_name cursor for select name from dbo.sysobjects where type='u' and name like 'Data_%20%' and right(name,10) < '2009-01-01' ;
//打开游标
open cursor_name;

// 从游标中fetch 信息
fetch next from cursor_name  into @name;
while (@@fetch_status = 0)
begin

//执行删除表
exec('drop table "' + @name + '"');
fetch next from cursor_name into @name;
end

//关闭游标
close cursor_name;
deallocate cursor_name;

随机文章:

This entry was posted on Thursday, April 2nd, 2009 at 16:08 and is filed under DataBase. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.