SQLSERVER:通过sys.tables实现批量删表、快速统计多表记录和

SQLSERVER:通过sys.tables实现批量删表,或者回滚表

begin try
    drop table #temp10
end try
begin catch
end catch

select 'drop/*truncate*/ table dbo.'+name as droptable,ROW_NUMBER() over(order by name) as rownumber 
into #temp10
from sys.tables where name like 'member%' and SCHEMA_NAME(schema_id)='dbo'

select * from #temp10

declare @rownumber int;
declare @records int;
declare @droptable  nvarchar(64);

select @records=MAX(rownumber) from #temp10

set @rownumber=1;

while @rownumber<=@records 
begin 
    select @droptable = droptable from #temp10 where rownumber=@rownumber
    print @droptable
    exec sp_executesql @droptable
    
    set @rownumber=@rownumber+1;
end

动态统计数据库，某表每秒插入记录量

declare @records int;
  
while 0=0
begin 
    waitfor delay '00:00:01'   -- 每间隔1s，执行一次
   
    select @records=isnull(sum(t10.rows),0)
    from sys.partitions as t10 
        inner join sys.objects as t11 on t10.object_id=t11.object_id
    where t11.name like 'member%' 
    and t10.partition_number=2 -- 当前数据插入到那个分区了,就写那个分区.如果没有分区,该条件可以不加
    and t10.index_id>=2
    
    print @records
end