删除某个数据库中的所有表

use databasename
go

 

--用@num来控制一次删除几个表
declare @name char(256),@xtype char(10),@sql char(1024),@num int,@rows int
declare @sql2 nvarchar(50),@sqlTemp varchar(50)
set @num=1

set @sqlTemp='select @rows =count(*) from ' --这个用来判断表中是否有数据，没有就没有必要再删除了

 

--申明并打开游标

declare sysobj_cur cursor
for select name,xtype from sysobjects where xtype='U' order by name

open sysobj_cur

fetch next from sysobj_cur into @name,@xtype
while (@@fetch_status<>-1 )
begin
 if(@num>1)
  break
 select  @sql2=@sqlTemp+@name

 exec sp_executesql @sql2,N'@rows int output',@rows output
 while(@rows<1)
 begin
  if (@@fetch_status = -1 )
   break
  fetch next from sysobj_cur into @name,@xtype
  select  @sql2=@sqlTemp+@name
  print @sql2
  exec sp_executesql @sql2,N'@rows int output',@rows output
  print @num
 end

 set @num = @num +1
 set @sql='truncate table'+@name   --用truncate table 去删除表中的数据
 exec(@sql)
 fetch next from sysobj_cur into @name,@xtype
end
close sysobj_cur
deallocate sysobj_cur
GO