自动删除用户表

declare @name varchar(100),@sql varchar(100),@drop varchar(max)
--@name存储用户表名
--@sql存储删除用户表语句
--@drop存储查询已删除的表名
set @drop='select name from ('
--循环查找用户表，如无用户名，则退出循环
while (select count(Name) from sysobjects where xtype='U' and status>=0)<>0
begin
select top 1 @name=name from sysobjects where xtype='U' and status>=0
set @sql='drop table '+@name
set @drop+='select '''+@name+''' as name union all '
--删除表
exec(@sql)
end
set @drop=left(@drop,LEN(@drop)-10)
set @drop+=') as DropTable'
--列出已删除的表名
exec(@drop)