IF object_id('tempdb..#temp') is not null BEGIN DROP TABLE #temp END DECLARE @index int , @count int , @schemaname varchar(50) , @tablename varchar(50) set @index=1 set @count=(select count(*) from sysobjects where xtype='U' AND name LIKE '%_itm' )----数据库中有多少个表select row_number() over(order by name) as rowNumber,name, ( SELECT a.name from sys.tables t inner join sys.schemas a ON t.schema_id=a.schema_id WHERE t.name=ob.name) as schemaname into #temp from sysobjects ob where xtype='U' AND name LIKE '%_itm' ---具体要显示的表名 -- select * from #tempWHILE(@index<@count) BEGIN set @schemaname=(SELECT schemaname from #temp where rowNumber=@index) set @tablename=(SELECT name from #temp where rowNumber=@index) exec('delete '+ @schemaname+'.'+@tablename) set @index=@index+1 END
-- LZ注意 : -- 条件要写成,下面的, 不然你的 a_itm 和 bitm 表,都会被清空, "_" 也是通配符where name like '%\_item' escape '\'
IF object_id('tempdb..#temp') is not null
BEGIN DROP TABLE #temp END
DECLARE @index int , @count int , @schemaname varchar(50) , @tablename varchar(50)
set @index=1
set @count=(select count(*) from sysobjects where xtype='U' AND name LIKE '%_itm' )----数据库中有多少个表select row_number() over(order by name) as rowNumber,name,
( SELECT a.name from sys.tables t inner join sys.schemas a ON t.schema_id=a.schema_id WHERE t.name=ob.name) as schemaname
into #temp from sysobjects ob where xtype='U' AND name LIKE '%_itm' ---具体要显示的表名
-- select * from #tempWHILE(@index<@count)
BEGIN
set @schemaname=(SELECT schemaname from #temp where rowNumber=@index)
set @tablename=(SELECT name from #temp where rowNumber=@index)
exec('delete '+ @schemaname+'.'+@tablename)
set @index=@index+1
END
-- LZ注意 :
-- 条件要写成,下面的, 不然你的 a_itm 和 bitm 表,都会被清空, "_" 也是通配符where name like '%\_item' escape '\'