SqlServer 使用存储过程一次清空大量的数据表
今日 学习sql server , 产生了不少数据 ， 挨个删除各个表中数据有点麻烦，遂写一下存储过程，来清空各个表，以便日后查阅，如另有高见，恳请赐教
?
create proc tbdel
@tables varchar(200)
as
DECLARE @pointerprev int?
?? ?DECLARE @pointercurr int?
declare @cursorstring varchar(30)
set @pointerprev = 1
set @tables = @tables + ','
?
create table #tmp1
(
tabname varchar(20)?
)
?
while(@pointerprev<len(@tables))
begin?
set @pointercurr = charindex(',',@tables,@pointerprev)
if(@pointercurr>0)
begin
insert into #tmp1 values(substring(@tables,@pointerprev,@pointercurr-@pointerprev))
SET @pointerprev = @pointercurr+1
end
else?
break
end
?
--insert into #tmp1 values(SUBSTRING(@tables,@pointerprev,LEN(@tables)-@pointerprev+1) 这句取消注释后，会报错，还没找到原因。。。
--select * from #tmp1;
?
declare mycursor cursor
for select * from #tmp1
open mycursor
fetch next from mycursor into @cursorstring
while(@@fetch_status=0)
begin?
set @cursorstring = 'delete from '+@cursorstring
print @cursorstring
exec(@cursorstring)
fetch next from mycursor into @cursorstring
end
close mycursor
deallocate mycursor
?
drop table #tmp1;
