方法一:
use DM
go
declare @name varchar(20)
while(exists(select * from sysobjects where name like 'epsps2%'))
begin
select @name=name from sysobjects where name like 'epsps2%'
exec ('drop table '+@name)
end
方法二:
-- 测试数据
create table dbo.temptb_1 (id int );
create table guest.temptb_2 (id int );
-- 打开隐式事务
SET IMPLICIT_TRANSACTIONS ON
-- 执行动态删除
--定义一个变量
declare @sql varchar(max);
set @sql = '' ;
select @sql = @sql + ' drop table ' +
--获取表名称,形如:dbo.temptb_***,escape '/'表示'/'为转义符号
QUOTENAME (SCHEMA_NAME ([schema_id])) + ' . ' + QUOTENAME ([name]) + ' ; '
from sys.tables where is_ms_shipped = 0 and [name] like ' temptb/_% ' escape ' /'
-- select @sql;
EXEC (@sql);
-- 检查删除是否正确
select * from sys.tables
-- 正确,则提交事务,确认删除
commit tran
-- 不正确,则回滚事务,取消删除
rollback tran
-- 关闭隐式事务
SET IMPLICIT_TRANSACTIONS OFF