declare @sql nvarchar(1000) declare c1 cursor for select 'drop table '+name from sysobjects where xtype='U' and name like 'abc_%' open c1 fetch next from c1 into @sql while @@fetch_status=0 begin exec(@sql) fetch next from c1 into @sql end close c1 deallocate c1
--把结果执行一下,不知你是要删除表还是删除表的记录? --删除表 select 'drop table '+name from sysobjects where xtype='u' and name like 'abc%' --删除表记录 select 'delete '+name from sysobjects where xtype='u' and name like 'abc%'
--写个游标,循环一下就OK了.declare @tablename varchar(10) declare @sql varchar(300) declare deltable cursor for select name from sysobjects where name like'acb%' and type='u'open deltablefetch next from deltable into @tablename while @@fetch_status=0 begin set @sql='drop table'+@tablename execute(@sql) fetch next from deltable into @tablename endclose deltable deallocate deltable
DECLARE @S VARCHAR(8000) SELECT 'DROP TABLE '+NAME FROM SYSOBJECTS WHERE TYPE='U' AND NAME LIKE 'ABC%' EXEC(@S)
如果删除记录,把drop 改为delete 就可以了.
declare @sql varchar(8000) set @sql='' select @sql=@sql+' drop table ['+name+'] ' from sysobjects where xtype='u' and name like 'abc%' exec(@sql)
declare @S varchar(8000) select @s=isnull(@s+' DROP TABLE ','DROP TABLE ')+NAME FROM SYSOBJECTS WHERE TYPE='U' AND NAME LIKE 'ABC%' exec(@s)
select 'drop table '+name from sysobjects where xtype='u' and name like 'abc%'
--忘賦值了 DECLARE @S VARCHAR(8000) SET @S='' SELECT @S='DROP TABLE '+NAME FROM SYSOBJECTS WHERE TYPE='U' AND NAME LIKE 'ABC%' EXEC(@S)
declare @Cmd nvarchar(1000) select @Cmd=isnull(@Cmd,'')+','+name from sysobjects where xtype='u' and name like 'abc%' set @Cmd='drop table ' + right(@Cmd,len(@Cmd)-1) exec(@Cmd)
--drop table exec sp_msforeachtable 'if ''?'' like ''/[dbo/]./[abc%'' escape ''/'' drop table ? ' --delete exec sp_msforeachtable 'if ''?'' like ''/[dbo/]./[abc%'' escape ''/'' delete ? '
declare c1 cursor for
select 'drop table '+name from sysobjects where xtype='U' and name like 'abc_%'
open c1
fetch next from c1 into @sql
while @@fetch_status=0
begin
exec(@sql)
fetch next from c1 into @sql
end
close c1
deallocate c1
--删除表
select 'drop table '+name from sysobjects where xtype='u' and name like 'abc%'
--删除表记录
select 'delete '+name from sysobjects where xtype='u' and name like 'abc%'
--写个游标,循环一下就OK了.declare @tablename varchar(10)
declare @sql varchar(300)
declare deltable cursor
for select name from sysobjects
where name like'acb%' and type='u'open deltablefetch next from deltable into @tablename
while @@fetch_status=0
begin
set @sql='drop table'+@tablename
execute(@sql)
fetch next from deltable into @tablename
endclose deltable
deallocate deltable
SELECT 'DROP TABLE '+NAME FROM SYSOBJECTS WHERE TYPE='U' AND NAME LIKE 'ABC%'
EXEC(@S)
set @sql=''
select @sql=@sql+' drop table ['+name+'] ' from sysobjects where xtype='u' and name like 'abc%'
exec(@sql)
select @s=isnull(@s+' DROP TABLE ','DROP TABLE ')+NAME FROM SYSOBJECTS WHERE TYPE='U' AND NAME LIKE 'ABC%'
exec(@s)
DECLARE @S VARCHAR(8000)
SET @S=''
SELECT @S='DROP TABLE '+NAME FROM SYSOBJECTS WHERE TYPE='U' AND NAME LIKE 'ABC%'
EXEC(@S)
select @Cmd=isnull(@Cmd,'')+','+name from sysobjects where xtype='u' and name like 'abc%'
set @Cmd='drop table ' + right(@Cmd,len(@Cmd)-1)
exec(@Cmd)
exec sp_msforeachtable 'if ''?'' like ''/[dbo/]./[abc%'' escape ''/'' drop table ? '
--delete
exec sp_msforeachtable 'if ''?'' like ''/[dbo/]./[abc%'' escape ''/'' delete ? '