【sql】sql数据监控

sql数据监控

• 通过配置表 tables_config

id dbname tablename cond1 jbt rpt_fact_custjbt_stat [日期]2 jbt rpt_fact_custmini [统计时间]12 jbt rpt_fact_custjbt_stat_acc [日期]4 jbt st_dd_custtrade_attr dcdate5 jbt st_dd_jbtmatch dcdate

• 实现监控每个数据库下表的记录数，可通过报表邮件发送

select * from   tables_check  where dcdate = 20170301 

• 存储过程

ALTER procedure [dbo].[proc_tables_check]( @dcdate  varchar(10) ,  @selectdb varchar(10), @insid int=0, @error int =0 output )as /*exec  proc_tables_check  20170630,'jbt'exec  proc_tables_check  20170630,'all'*/begin  declare  @date0  int;select  @date0=max(dcdate) from [172.x.x.x].cgds2016.dbo.trddate where  dcdate < @dcdate   if (select  COUNT(*)  from [172.x.x.x].cgds2016.dbo.trddate where dcdate =@dcdate )= 0 return  1 --节假日返回declare @sql varchar(max)declare @tb_cur  as cursordeclare @tb_name varchar(128)declare @dbname varchar(64)declare @id   int declare @cond   varchar(max) create table #tables_check ( [dcdate] [varchar](10) NULL, [dbname] varchar(64) ,  [tablename] [varchar](256) NULL, id int , [cnt] [int] NULL, )if (@selectdb = 'all')set @tb_cur =  CURSOR FOR select  dbname, tablename , id , cond  from  jbt..tables_config  where   1=1else set @tb_cur =  CURSOR FOR select  dbname, tablename , id , cond  from  jbt..tables_config  where  dbname =  @selectdb open @tb_curfetch  NEXT FROM @tb_cur into  @dbname,@tb_name,@id ,@cond while  @@FETCH_STATUS=0  begin set @sql = 'insert into  #tables_check (dcdate,dbname, tablename,id,cnt) select ' + @dcdate +','+ '''$dbname''' +',' +'''$tb_name''' +',' + '$id'+','+'count(*)  from  $dbname.dbo.$tb_name with(nolock) where ' +@cond+'=$dcdate' print  @sql  set @sql =  replace(replace(REPLACE(REPLACE(@sql, '$tb_name', @tb_name) ,'$id', @id),'$dcdate',@dcdate),'$dbname',@dbname) print @sql  exec GDW..proc_run_sql @sql, @insid, @error   fetch  NEXT FROM @tb_cur into @dbname,@tb_name, @id ,@cond end end---入库if (@selectdb = 'all') beginset @sql   =  'delete from  jbt..tables_check where dcdate = $dcdate'set @sql =  replace(REPLACE(@sql, '$dcdate', @dcdate),'$selectdb',@selectdb)exec  GDW..proc_run_sql  @sql, @insid,@error end else  begin set @sql   =  'delete from  jbt..tables_check where dcdate = $dcdate and dbname = ' + '''$selectdb'''set @sql =  replace(REPLACE(@sql, '$dcdate', @dcdate),'$selectdb',@selectdb)exec  GDW..proc_run_sql  @sql, @insid,@error end insert into jbt..tables_check select  @dcdate as dcdate ,a.dbname,a.tablename,a.id ,ISNULL(cnt,0) cnt   from tables_config a left join  #tables_check  b on  a.dbname = b.dbname and  a.tablename = b.tablename