一共2万个多个表。
执行这句:SELECT top 10 name FROM SYSOBJECTS WHERE TYPE='U' 数据如下:
name
_34_JHXSM
_33_RMKYLIAN
_31_XUSHOULIAN477
_33_LIANHUA_YJ
_34_CHENGFANG_GUO
_42_HUAHHUANG
_43_YAYAHANGUO
_41_CAISHAOHUA
_20_WOSHIOSP
_30_SHYE
……现在我想把所有表中满足如下条件的删除。
delete 表名 where _time< '2009-2-5'表名这里要循环更换2万次,要怎么弄?
求动态SQL语句。
执行这句:SELECT top 10 name FROM SYSOBJECTS WHERE TYPE='U' 数据如下:
name
_34_JHXSM
_33_RMKYLIAN
_31_XUSHOULIAN477
_33_LIANHUA_YJ
_34_CHENGFANG_GUO
_42_HUAHHUANG
_43_YAYAHANGUO
_41_CAISHAOHUA
_20_WOSHIOSP
_30_SHYE
……现在我想把所有表中满足如下条件的删除。
delete 表名 where _time< '2009-2-5'表名这里要循环更换2万次,要怎么弄?
求动态SQL语句。
delete from sysobjects where
uid = 1 and crdate < '2009-2-5' and xtype ='U'
可以通过
SELECT USER_ID()
查看当前架构
按F5执行,可能会碰到一个错误提示:“未启用对系统目录的特殊更新。系统管理员必须重新配置 SQL Server 以允许这种操作。” 解决这个小小的问题的方法是:
打开mssql server 属性---》服务器选项----》启用对系统目录的特殊更新(允许对系统目录进行直接修改) 即可。
(企业管理器---》工具---》SQL Server 配置属性---》服务器设置---》允许对系统目录直接进行修改)之后重新在查询分析器中执行刚才的sql语句。
declare mycursor cursor for SELECT name FROM SYSOBJECTS WHERE TYPE='U' and name like '%_ASD_%' --为所获得的数据集指定游标
open mycursor --打开游标
fetch next from mycursor into @NAME --开始抓第一条数据
while(@@fetch_status=0) --如果数据集里一直有数据
begin
delete FROM @NAME where _time< '2009-2-5' --开始做想做的事(什么更新呀,删除呀)
fetch next from mycursor into @NAME --跳到下一条数据
end
close mycursor --关闭游标
deallocate mycursor --删除游标
我写成上面这样了,但提示我
服务器: 消息 137,级别 15,状态 2,行 7
必须声明变量 '@NAME'。
declare @name varchar(100)
declare cur cursor for select name FROM SYSOBJECTS WHERE TYPE='U'
open cur
fetch from cur into @name
while @@fetch_status=0
begin
set @s='delete '+@name +' where _time < ''2009-2-5'''
print @s
exec(@s)
fetch next from cur into @name
end
close cur
deallocate cur
declare @NAME varchar(50) --定义变量来保存ID号
declare @sql varchar(8000)
declare mycursor cursor for SELECT name FROM SYSOBJECTS WHERE TYPE='U' and name like '%_ASD_%' --为所获得的数据集指定游标
open mycursor --打开游标
fetch next from mycursor into @NAME --开始抓第一条数据
while(@@fetch_status=0) --如果数据集里一直有数据
begin
set @sql='delete '+@NAME +' where _time < ''2009-2-5'''
fetch next from mycursor into @NAME --跳到下一条数据
end
close mycursor --关闭游标
deallocate mycursor --删除游标
declare @NAME nvarchar(50)
declare mycursor cursor for SELECT name FROM SYSOBJECTS WHERE TYPE='U'
open mycursor
fetch next from mycursor into @NAME
while(@@fetch_status=0)
begin
delete FROM [Table1].[dbo].str(@NAME) where _time< '2009-2-5'
fetch next from mycursor into @NAME
end
close mycursor
deallocate mycursor 应该没问题了吧
declare @NAME varchar(50) --定义变量来保存ID号
declare @sql varchar(8000)
declare mycursor cursor for SELECT name FROM SYSOBJECTS WHERE TYPE='U' and name like '%_ASD_%' --为所获得的数据集指定游标
open mycursor --打开游标
fetch next from mycursor into @NAME --开始抓第一条数据
while(@@fetch_status=0) --如果数据集里一直有数据
begin
set @sql='delete '+@NAME +' where _time < ''2009-2-5'''
fetch next from mycursor into @NAME --跳到下一条数据
exec(@sql)end
close mycursor --关闭游标
deallocate mycursor --删除游标
总结了你们两个写的,最后就这样啦。
declare @s varchar(8000)
declare @name varchar(100)
declare cur cursor for select name FROM SYSOBJECTS WHERE TYPE='U' and name like '%_asd_%'
open cur
fetch from cur into @name
while @@fetch_status=0
begin
set @s='delete [t1].[stat1].'+@name +' where _time < ''2009-2-5'''
print @s
exec(@s)
fetch next from cur into @name
end
close cur
deallocate cur
为什么不写from?