sql server 批量删除表

根据名称

declare @name varchar(max) 

declare @sql varchar(max) 
while(exists(select * from sysobjects where name like '%bak%' and type='u' ))
begin 

   select @name=name from sysobjects where name like '%bak%' and type='u'
   set @sql='drop table ['+@name+']'
   print @sql
   exec(@sql)

end

根据行数

declare @name varchar(30) 


while(exists(select * from (
SELECT   a.name, b.rows
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') 
AND (b.indid IN (0, 1)) 
and b.rows>=10000000


)t))
begin select @name=name from 
(SELECT   a.name, b.rows
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') 
AND (b.indid IN (0, 1)) 
and b.rows>=10000000


)t  
exec ('drop table ['+@name+']') 
end