用动态sql实现报表数据的检测监控

         工作中需要对一个每天同步数据的报表数据库做监控，每次发现页面上数据没有了再去查搬运日志或数据库表很繁琐，无意中看到了数据库的邮件发送功能，做了一个存储过程使用动态sql来对所有表做一个检测，发现没有数据的表发送邮件和表名，这样直接根据邮件去检查单独异常的表搬运即可。

         之后还利用公司的短信发送平台，做了一个短信发送，更加及时了，但是那个需要开发支持，我不清楚细节，这里就不发了。

       创建数据库的邮件功能是从网上看来的，这里不做详述，只贴个例子

--1.启用Database Mail扩展存储过程
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH override
GO
sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE WITH override
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH override
GO
 
--2.添加account
exec msdb..sysmail_add_account_sp
        @account_name            = 'test' --邮件帐户名称SQL Server 使用
       ,@email_address           = 'test'@163.com' --发件人邮件地址
       ,@mailserver_name         = 'mail.btte.net'        --邮件服务器地址
       ,@mailserver_type         = 'SMTP'                --邮件协议SQL 2005只支持SMTP
       ,@port                    = 25                    --邮件服务器端口
       ,@username                = 'test' --用户名
       ,@password                = '******' --密码
        
--3.添加profile
exec msdb..sysmail_add_profile_sp
@profile_name = 'test_mail'-- profile 名称
   ,@description  = 'dba test mail profile'-- profile 描述
   ,@profile_id   = null
    
--4.映射account和profile
exec msdb..sysmail_add_profileaccount_sp  
@profile_name    = 'test_mail'-- profile 名称
   ,@account_name    = 'test'-- account 名称
   ,@sequence_number = 1-- account 在profile中顺序
 

以下是检测的存储过程语句，

USE [jeeplus]

GO
/****** Object:  StoredProcedure [dbo].[report_table_check]    Script Date: 2017/11/21 15:52:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*2017年11月20日14:43:06改进，检测报表数据是否正常更新*/

ALTER PROCEDURE [dbo].[report_table_check]

AS
BEGIN

SET NOCOUNT ON
    declare @check_reslut nvarchar(1000)

    DECLARE Cursor_Table CURSOR FOR
      /*1.用游标Cursor_Table，遍历需要检测的表，这里我列出了需要检测的表，因为报表数据库不是所有的表格都需要检查*/
    SELECT name from jeeplus.sys.sysobjects WHERE xtype = 'u' AND name in(
    'base_account_visual',
    'fact_IFMS',
    'log_sys_Community',
    'Report_Accounts_list',
    'Report_community',
    'Report_community_operate',
    'Report_Consult_NEW',
    'Report_Consult_Renewals',
    'Report_cti_statistics',
    'Report_Index_Ship_statics',
    'Report_Install_list',
    'Report_InstallForm_TBL',
    'Report_Person',
    'Report_product_sale',
    'Report_Reminder',
    'Report_Repair_list',
    'Report_RepairForm',
    'Report_RepairStatistics',
    'Report_revenue',
    'Report_Simple_Day',
    'Report_statistics',
    'report_zcqk',
    'WorkGroup_TBL',
    'sys_Community'
    ,'jeeps_workflow_returns_refund'
    ,'jeeps_workflow_returns_list'
    ,'jeeps_account_audit'
    ,'jeeps_workflow_complaint_list'
    ,'jeeps_workflow_list'
    ,'jeeps_workflow_extensive_fault_list'
    ,'jeeps_centralized_input_workload_list'
    ,'jeeps_workflow_Account_workload_list'
    ,'jeeps_workflow_repair_workload_list'
    ,'jeeps_workflow_technology_filtration_workload_list'
    ,'jeeps_Reminder_list'
    )
    /*这里我列出了需要检测的表，因为报表数据库不是所有的表格都需要检查*/
    
    
    declare @time datetime
    DECLARE @DynamicSQLText NVARCHAR(MAX)
    DECLARE @tableName NVARCHAR(200);
    DECLARE @CurrentTableCount NVARCHAR(4000)
    DECLARE @columnName nvarchar(50)

    set @columnName=''

    set @DynamicSQLText=''
   set @time=  dateadd(ms,-1,DATEDIFF(DAY, 1, GETDATE() ))

    OPEN Cursor_Table
    FETCH NEXT from Cursor_Table INTO @tableName
    WHILE @@fetch_status = 0

    BEGIN
    print  QUOTENAME(@tableName)
    set @columnName=''
    set @DynamicSQLText=''
/*对表里的字段做一个排序，因为当时设计的时候没考虑这一点，所以每个表的时间字段名字都不一样，做了一个条件排查*/
    if not exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name in ('create_date','dispatch_time','logtime','create_time','statisticstime','timee','createtime','time')) /*这里列出来所有表用于比较的时间字段，如果都没有直接返一个报错，后续添加即可*/
    begin
        print @tableName+'没有timee字段'
    end
    else
    begin
        IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name='timee')
        BEGIN
        set @columnName='timee'
        END
        ELSE IF exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name='statisticstime')
        BEGIN
        set @columnName='statisticstime'
        END
        ELSE IF  exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='create_time')
        BEGIN
        set @columnName='create_time'
        END
        ELSE IF  exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='logtime')
        BEGIN
        set @columnName='logtime'
        END
        ELSE IF  exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='create_date')
        BEGIN
        set @columnName='create_date'
        END
        ELSE IF  exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='createtime')
        BEGIN
        set @columnName='createtime'
        END
        ELSE IF  exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='dispatch_time')
        BEGIN
        set @columnName='dispatch_time'
        END
        ELSE IF  exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='dispatch_time')
        BEGIN
        set @columnName='dispatch_time'
        END
        ELSE IF  exists ( select name FROM jeeplus.sys.SysColumns WHERE ID = Object_Id( @tableName ) and name ='time')
        BEGIN
        set @columnName='time'
        END

        PRINT @columnName
        set @CurrentTableCount= '根据'+ QUOTENAME(@columnName) + '字段判断，以下表未更新最新数据:[jeeplus].[dbo].'+ QUOTENAME(@tableName)
        set @DynamicSQLText  =
        'DECLARE @maxtime datetime

        SELECT @maxtime=convert (datetime,max('+QUOTENAME(@columnName)+'),120) FROM [jeeplus].[dbo].' + QUOTENAME(@tableName) + '


        IF  @maxtime>=@TIMET
        BEGIN
        print ''以下表有最新数据: ' + QUOTENAME(@tableName) + '''
        END
        else
        BEGIN
        exec msdb..sp_send_dbmail @profile_name = ''check'',@recipients =''z****@163.com'',@subject =''数据库表数据异常提醒'',@body =@CurrentTableCountT ,@body_format =''TEXT''


        print @CurrentTableCountT
        END
        '
        EXEC sp_executesql  @DynamicSQLText ,N'@CurrentTableCountT NVARCHAR(4000),@TIMET datetime',@TIMET=@time ,@CurrentTableCountT=@CurrentTableCount
      
    end


    FETCH NEXT from Cursor_Table INTO @tableName;

    END

    CLOSE Cursor_Table;
    DEALLOCATE Cursor_Table;
END