思路:到对象表获取所有的单据对应的单据头,使用游标统计数据量并记录
SQL:
--系统单据数量头统计
if exists (select 1 from sys.tables where name='tmpbillcount')
drop table tmpbillcount
go
create table tmpbillcount(fidentity int identity(0,1), FNAME nvarchar(200),FSUBSYSID nvarchar(200),FSUPPLIERNAME nvarchar(100),ftablename nvarchar(100),fcount int)
go
insert into tmpbillcount(FNAME,FSUBSYSID,FSUPPLIERNAME,ftablename,fcount)
select FNAME,FSUBSYSID,FSUPPLIERNAME,case when CharIndex('<TableName>',billhead)>0 then SUBSTRING(billhead,CharIndex('<TableName>',billhead)+11,CharIndex('</TableName>',billhead)-CharIndex('<TableName>',billhead)-11) else null end tablename,convert(int,0) fcount
from
(
select b.FNAME,FSUBSYSID,FSUPPLIERNAME, FKERNELXML,substring(convert(nvarchar(max),FKERNELXML),CharIndex('<HeadEntity',convert(nvarchar(max),FKERNELXML)),CharIndex('HeadEntity>',convert(nvarchar(max),FKERNELXML))) as billhead
from T_META_OBJECTTYPE a join T_META_OBJECTTYPE_L b on a.FID=b.FID and FLOCALEID=2052 where FMODELTYPEID='100' and FDEVTYPE<>2
) T
where CharIndex('<TableName>',billhead)>0
order by FSUBSYSID
go
DECLARE @TbName VARCHAR(80)
declare @fidentity int
declare @total int
select @total= COUNT(1) from tmpbillcount
DECLARE recordcound SCROLL CURSOR FOR
select fidentity,ftablename from tmpbillcount
order by fidentity
OPEN recordcound
FETCH recordcound INTO @fidentity,@TbName
WHILE @@FETCH_STATUS<>-1
BEGIN
exec(
'if exists (select 1 from sys.objects where name='''+@TbName+''' and type in (''U'',''V''))
update tmpbillcount set fcount=(select COUNT(1) from '+@TbName+') where ftablename='''+@TbName+'''
else
update tmpbillcount set fcount=null where ftablename='''+@TbName+''''
)
print @TbName+' has finished! processing:'+convert(varchar(10),@fidentity+1)+' of '+convert(varchar(10),@total)
FETCH recordcound INTO @fidentity,@TbName
END
CLOSE recordcound
DEALLOCATE recordcound
go
--查询收集到的结果
select * from tmpbillcount order by fcount desc
执行过程打印查询的进度:
执行结果: