SQL 游标如何赋值给变量,并在DROP TABLE 语句中引用变量? 实现批量删除相同名称开头的表
说明:数据库EXAM11中有较多以LSJ_ 或SJ_ 或SJOK_开头的表名,想实现批量删除它们?(批量删除表名:表名都以LSJ_ 或SJ_ 或SJOK_开头)
目前用:select TABLE_NAME from information_schema.tables where TABLE_NAME LIKE 'LSJ_%'or TABLE_NAME LIKE 'SJ_%' or TABLE_NAME LIKE 'SJOK_%' 可以查询出这些表名。
我设计的SQL 游标赋值删除代码如下:
use exam11
declare @name varchar(255)
declare cursor1 cursor for
select TABLE_NAME from information_schema.tables where TABLE_NAME LIKE 'LSJ_%'or TABLE_NAME LIKE 'SJ_%' or TABLE_NAME LIKE 'SJOK_%'
open cursor1
fetch next from cursor1 into @name
while @@fetch_status=0
begin
--select @name --注:当用此语名可以执行
drop table @name --注:此语名提示:第 11 行: '@name' 附近有语法错误。
fetch next from cursor1 into @name
end
close cursor1
deallocate cursor1
却提示:
服务器: 消息 170,级别 15,状态 1,行 11
第 11 行: '@name' 附近有语法错误。
这是为何,麻烦大家批点一下,谢谢。
[解决办法]
drop table @name
--->
set @name = 'drop table' +@name
exec(@name)
[解决办法]
try this,
use exam11declare @name varchar(255),@sql varchar(6000)declare cursor1 cursor for select TABLE_NAME from information_schema.tables where TABLE_NAME LIKE 'LSJ_%'or TABLE_NAME LIKE 'SJ_%' or TABLE_NAME LIKE 'SJOK_%'open cursor1fetch next from cursor1 into @namewhile @@fetch_status=0begin --select @name --注:当用此语名可以执行 select @sql='drop table '+@name exec(@sql) fetch next from cursor1 into @nameend close cursor1deallocate cursor1