这篇文章主要介绍了数据库中tmstamp monitor的示例代码，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

USE[DBCenter]GO/******Object:StoredProcedure[dba].[GetRowDiff]ScriptDate:2017/5/813:06:50******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOalterprocedure[dba].[GetRowDiff]asdeclare@databse_namevarchar(15),@schema_namevarchar(10),@table_namevarchar(100),@max_tmstamp_2bigint,@row_count_2bigint,@max_tmstamp_1bigint,@row_count_1bigint,@datetimedatetime,@sqlvarchar(8000),@record_time_1varchar(19),@record_time_2varchar(19)SETNOCOUNTonset@sql=''set@datetime=getdate()truncatetableDBCenter..viewTMstamp_diffdeclaremycursorcursorforselectaa.databse_name,aa.[schema_name],aa.table_name,aa.max_tmstampasmax_tmstamp_2,aa.row_countasrow_count_2,bb.max_tmstampasmax_tmstamp_1,bb.row_countasrow_count_1,convert(varchar(19),aa.record_time,120)asrecord_time_2,convert(varchar(19),bb.record_time,120)asrecord_time_1from(selecta.databse_name,a.[schema_name],a.table_name,a.max_tmstamp,a.row_count,a.record_timefrom[DBCenter].[dbo].[viewMaxTMtamp]awith(nolock)whereconvert(varchar(10),a.record_time,120)+''+convert(varchar(2),a.record_time,114)=convert(varchar(10),dateadd(hh,0,getdate()),120)+''+convert(varchar(2),dateadd(hh,0,getdate()),114))asaajoin(selectb.databse_name,b.[schema_name],b.table_name,b.max_tmstamp,b.row_count,b.record_timefrom[DBCenter].[dbo].[viewMaxTMtamp]bwith(nolock)whereconvert(varchar(10),b.record_time,120)+''+convert(varchar(2),b.record_time,114)=convert(varchar(10),getdate(),120)+''+convert(varchar(2),dateadd(hh,-1,getdate()),114))asbbonaa.databse_name=bb.databse_nameandaa.[schema_name]=bb.[schema_name]andaa.table_name=bb.table_name--打开游标openmycursor--从游标里取出数据赋值到我们刚才声明的2个变量中fetchnextfrommycursorinto@databse_name,@schema_name,@table_name,@max_tmstamp_2,@row_count_2,@max_tmstamp_1,@row_count_1,@record_time_2,@record_time_1--判断游标的状态--0fetch语句成功---1fetch语句失败或此行不在结果集中---2被提取的行不存在while(@@fetch_status=0)beginset@sql='insertintoDBCenter..viewTMstamp_diff([db_name],[schema_name],[table_name],[row_count_diff],[tmstmp_row_count_diff],[tmstmp_diff],[record_time_2],[record_time_1],[date_time])select'+''''+@databse_name+''''+','+''''+@schema_name+''''+','+''''+@table_name+''''+','+cast((@row_count_2-@row_count_1)asvarchar(100))+'asrow_count_diff,count(1)astmstmp_row_count_diff,'+cast((@max_tmstamp_2-@max_tmstamp_1)asvarchar(100))+'astmstmp_diff,'+''''+convert(varchar(19),@record_time_2,120)+''''+','+''''+convert(varchar(19),@record_time_1,120)+''''+','+''''+convert(varchar(19),getdate(),120)+''''+'from'+@databse_name+'.'+@schema_name+'.'+@table_name+'with(nolock)wherecast(TMSTAMPasbigint)>='+cast(@max_tmstamp_1asvarchar(100))+'andcast(TMSTAMPasbigint)<'+cast(@max_tmstamp_2asvarchar(100))--print@sqlEXEC(@sql)set@sql='insertintoDBCenter..viewTMstamp_diff_his([db_name],[schema_name],[table_name],[row_count_diff],[tmstmp_row_count_diff],[tmstmp_diff],[record_time_2],[record_time_1],[date_time])select'+''''+@databse_name+''''+','+''''+@schema_name+''''+','+''''+@table_name+''''+','+cast((@row_count_2-@row_count_1)asvarchar(100))+'asrow_count_diff,count(1)astmstmp_row_count_diff,'+cast((@max_tmstamp_2-@max_tmstamp_1)asvarchar(100))+'astmstmp_diff,'+''''+convert(varchar(19),@record_time_2,120)+''''+','+''''+convert(varchar(19),@record_time_1,120)+''''+','+''''+convert(varchar(19),getdate(),120)+''''+'from'+@databse_name+'.'+@schema_name+'.'+@table_name+'with(nolock)wherecast(TMSTAMPasbigint)>='+cast(@max_tmstamp_1asvarchar(100))+'andcast(TMSTAMPasbigint)<'+cast(@max_tmstamp_2asvarchar(100))--print@sqlEXEC(@sql)fetchnextfrommycursorinto@databse_name,@schema_name,@table_name,@max_tmstamp_2,@row_count_2,@max_tmstamp_1,@row_count_1,@record_time_2,@record_time_1end--关闭游标closemycursor--撤销游标DEALLOCATEmycursorSETNOCOUNToffGO

USE[datayesdb]GO/******Object:StoredProcedure[dba].[GetMaxTMstmp]ScriptDate:2017/5/814:07:04******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATEprocedure[dba].[GetMaxTMstmp]@databasevarchar(20),@schemavarchar(20),@tbnamenvarchar(100),@datetimedatetimeasdeclare@Max_TMstmpbigintdeclare@sqlnvarchar(4000)declare@sql2nvarchar(4000)declare@iint=0DECLARE@startDate1DATE;DECLARE@startDateDATETIME;DECLARE@endDateDATETIME;SETNOCOUNTonSET@startDate1=GETDATE();SELECT@startDate=DATEADD(DAY,-0,@startDate1);SET@endDate=DATEADD(d,+1,CONVERT(DATETIME,@startDate1));--SELECT@startDatestartDate,@endDateendDate;--set@tbname='bond'--print@tbnamebegin--print@tbnameset@sql2='select@i=count(1)fromsys.columnscolwith(nolock)joinsys.tablestblwith(nolock)oncol.object_id=tbl.object_idwheretbl.name='+''''+@tbname+''''+'andcol.namein('+''''+'TMSTAMP'+''''+','+''''+'UPDATE_TIME'+''''+')andtbl.type='+''''+'U'+''''+'andtbl.schema_id=schema_id('+''''+@schema+''''+')'--print@sql2execsp_executesql@sql2,N'@iintout',@iout--print@iifisnull(@i,0)=2beginset@sql='insertinto[DBCenter].[dbo].[viewMaxTMtamp]([databse_name],[schema_name],[table_name],[min_tmstamp],[max_tmstamp],[row_count],[min_update_time],[max_update_time],[record_time])select'+''''+@database+''''+','+''''+@schema+''''+','+''''+@tbname+''''+',isnull(cast(min(TMSTAMP)asbigint),0)'+',isnull(cast(max(TMSTAMP)asbigint),0)'+',count(1)'+',min(UPDATE_TIME)'+',max(UPDATE_TIME),'+''''+cast(@datetimeasvarchar(20))+''''+'from'+@database+'.'+@schema+'.'+@tbname+'with(nolock)'--print@sqlEXEC(@sql)endendSETNOCOUNToff-------------------------------------------------------------------------------------------------------------------------GO

USE[datayesdb]GO/******Object:StoredProcedure[dba].[GetMaxTMstmp_job]ScriptDate:2017/5/814:07:45******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATEprocedure[dba].[GetMaxTMstmp_job]asdeclare@databasevarchar(20),@schemavarchar(20),@tblnamevarchar(100),@datetimedatetimeSETNOCOUNTonset@datetime=GETDATE()set@database='datayesdb'declaremycursorcursorforselectschema_name(schema_id)[schema],namefromsys.tableswith(nolock)wheretype='U'orderby[schema],name--打开游标openmycursor--从游标里取出数据赋值到我们刚才声明的2个变量中fetchnextfrommycursorinto@schema,@tblname--判断游标的状态--0fetch语句成功---1fetch语句失败或此行不在结果集中---2被提取的行不存在while(@@fetch_status=0)begin--print@tblnameexecdba.GetMaxTMstmp@database,@schema,@tblname,@datetimefetchnextfrommycursorinto@schema,@tblnameend--关闭游标closemycursor--撤销游标DEALLOCATEmycursorSETNOCOUNToffGO

感谢你能够认真阅读完这篇文章，希望小编分享的“数据库中tmstamp monitor的示例代码”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!