1.使用开窗函数先获取到表名(获取到的表名是字符不能直接使用),插入到临时表
select name,row_number() over(order by name) as num into #a
from (select name from dbo.sysobjects where name like '%xxx%')A
2.查看临时表存储的表名
select * from #a
3.使用循环删除表
declare @i int = 1
while(@i <= (select max(num) from #a))
declare @sql nvarchar(500) = 'drop table ' + (select name from #a where @i = num)
exec sp_executesql @sql
set @i = @i + 1