--如果表不多,可以这样
declare @s nvarchar(4000)
set @s=''
select @s=@s+',['+name+']' from sysobjects where xtype='U' and name like 'mms%'
if @@rowcount=0 return
set @s=stuff(@s,1,1,'')
exec('drop table '+@s)
--如果表多,则用游标保险
exec sp_msforeachtable 'if ''?'' like ''[[]''+user_name()+''].[[]#qt%]'' drop table ?'
**************************
晕倒,我上面的错了,你可千万别执行啊
你要执行了,哭都来不及了
就把你所有的表都给删啦
都快我忘了where子句
看看下面这个吧
*****************************
declare @tbname nvarchar(40),
@loop int
if exists(select * from sysobjects where name like 'mms%' and objectproperty(id,N'isusertable') = 1)
select id = identity(int,1,1),
tbname = name
into #temp01
from sysobjects
where name like 'mms%'
and objectproperty(id,N'isuertable') = 1
set @loop = @@rowcount
while @loop >= 1
begin
select @tbname = tbname
from #temp01
where id = @loop
execute ('drop table' + @tbname)
set @loop = @loop - 1
end
declare @tbname nvarchar(40),
@loop int
if exists(select * from sysobjects where name like 'mms%' and objectproperty(id,N'isusertable') = 1)
select id = identity(int,1,1),
tbname = name
into #temp01
from sysobjects
set @loop = @@rowcount
while @loop >= 1
begin
select @tbname = tbname
from #temp01
where id = @loop
execute ('drop table' + @tbname)
set @loop = @loop - 1
end
declare @tb varchar(50)
while exists(select * from sysobjects where xtype='U' and name like 'mms%')
begin
select @tb=name from sysobjects where xtype='U' and name like 'mms%'
exec('drop table '+@tb)
end