数据库中tmstamp监控的示例代码

使用 [DBCenter]GO/***** 对象：StoredProcedure [dba].[GetRowDiff] 脚本日期：2017/5/8 13: 06:50 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOalter 过程 [dba].[GetRowDiff] asdeclare @databse_name varchar(15),@schema_name varchar(10),@table_name varchar(100),@max_tmstamp_2 bigint,@ row_count_2 bigint、@max_tmstamp_1 bigint、@row_count_1 bigint、@datetime datetime、@sql varchar(8000)、@record_time_1 varchar(19)、@record_time_2 varchar(19)SET NOCOUNT 起始值 @sql=''set @datetime =getdate()截断表 DBCenter..viewTMstamp_diff声明 mycursor 光标用于选择 aa.databse_name,aa.[schema_name],aa.table_name,aa.max_tmstamp as max_tmstamp_2,aa.row_count as row_count_2,bb.max_tmstamp as max_tmstamp_1,bb.row_count as row_count_1,     convert(varchar(19), aa.record_time ,120) as record_time_2,convert(varchar(19),bb.record_time,120) as record_time_1 from ( ) select a.databse_name,a.[ schema_name],a.table_name,a.max_tmstamp,a.row_count,a.record_time 来自 [DBCenter].[dbo].[viewMaxTMtamp] a with (nolock) where where Convert(varchar(10),a.record_time,120) +' '+convert(varchar(2),a.record_time,114) = 转换(varchar(10),dateadd(hh,0,getdate()),120)+' '+convert(varchar(2),dateadd (hh,0,getdate()),114) ) as aa join join (从 [DBCenter] 选择 b.databse_name,b.[schema_name],b.table_name,b.max_tmstamp,b.row_count,b.record_time。[ dbo].[viewMaxTMtamp] b with (nolock) 其中convert( varchar(10),b.record_time,120)+' '+convert(varchar(2),b.record_time,114) = 转换(varchar(10), getdate(),120)+' '+convert(varchar( 2),dateadd(hh,-1,getdate()),114) ) 作为 aa.databse_name = bb.databse_name 和 aa 上的 bb。从游标中取出数据，将值赋给我们刚才的语句中的2个变量ables, fetch next from mycursor into @databse_name,@schema_name,@table_name,@max_tmstamp_2,@row_count_2,@max_tmstamp_1,@row_count_1,@record_time_2,@record_time_1 -- 确定游标的状态 -- 0 fetch 语句成功 -- - 1 提取语句失败或该行不在结果集中 ---2 提取的行不存在 while (@@fetch_status=0)   begin set @sql='insert into DBCenter..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] ) 选择 '+''''+@databse_name+''''+','+ ''''+@schema_name+''''+','+''''+@table_name+''''+', '+cast((@row_count_2-@row_count_1)as varchar(100))+' as row_count_diff,count(1) as tmstmp_row_count_diff ,' +cast((@max_tmstamp_2-@max_tmstamp_1)as varchar(100))+' as tmstmp_diff ,'+''''+convert(varchar(19),@record_time_2,120) +''''+','+''''+convert(varchar(19),@record_time_1,120)+'' ''+','+''''+convert(varchar(19),getdate (),120)+''''+' 来自 '+@databse_name+'.'+@schema_name+'.'+@table_name+' with (nolock) where cast(TMSTAMP as bigint) >=' +cast(@max_tmstamp_1 as varchar(100))+' and cast(TMSTAMP as bigint)<' +cast(@max_tmstamp_2 as varchar(100)) --print @sql EXEC (@sql) set @sql='插入 DBCenter..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]) 选择 '+''''+@databse_name+''''+','+''''+@schema_name+''''+ ','+''''+@table_name+''''+','+cast((@row_count_2-@row_count_1)as varchar(100))+' as row_count_diff,count(1) as tmstmp_row_count_diff,' +cast ((@max_tmstamp_2-@max_tmstamp_1)as varchar(100))+' as tmstmp_diff,'+''''+convert(varchar (19),@record_time_2,120)+''''+','+'' ''+convert(varchar(19),@record_time_1,120)+''''+','+'''' +convert(varchar(19),getdate(),120)+''''+'来自 '+@databse_name+'.'+@schema_name+'.'+@table_name+' with (nolock) where cast(TMSTAMP as bigint) >='+cast(@max_tmstamp_1 as varchar(100))+' 和cast(TMSTAMP as bigint)<'+cast(@max_tmstamp_2 as varchar(100)) --print @sql EXEC (@sql) fetch next from mycUrsor Into@databse_name,@schema_name,@table_name,@max_tmstamp_2,@row_count_2,@max_tmstamp_1, @row_count_1,@record_time_2,, @Record_time_1 结束Caladia Mycursor-重访游标 DEALLOCATE Mycursor Set NoT Nocount Office 

使用 [datayesdb]GO/*** *** 对象：StoredProcedure [dba].[GetMaxTMstmp] 脚本日期：2017/5/8 14:07:04 *** ***/set aNSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGocreate PROCEDURE [DBA]。 [GetmaxTMSTMP] @DataBase Varchar (20)、@schema varchar (20)、@tbname nvarch AR (100)、@DateTime DateTimeasDeclare @Max_tmstmp BigintDeClare @sql NVARCHAR (4000) 声明 @sql2 nvarchar(4000)declare @i int=0DECLARE @startDate1 DATE;声明@startDate DATETIME;声明@endDate DATETIME;设置NOCOUNT onSET @startDate1=GETDATE();SELECT @startDate=DATEADD(DAY,-0,@startDate1); SET @endDate=DATEADD(d,+1,CONVERT(DATETIME,@startDate1)); --SELECT @startDate 开始日期,@endDate endDate;--set @tbname='bond'--print @tbnamebegin--print @tbnameset @sql2='select @i=count(1) from sys.columns col with (nolock) join sys.tables tbl with ( nolock) on col.object_id=tbl.object_id where tbl.name='+'''' + @tbname+''''+' 和 col.name in ('+''''+'TMSTAMP'+''' '+','+''''+'UPDATE_TIME'+''''+') 和 tbl.type='+''''+'U'+''''+ ' 和 tbl.schema_id =schema_id ('+''''+ @schema+''''+')' --print @sql2exec sp_executesql @sql2,N'@i int out',@i out--print @iif isnull(@i,0) =2beginset @sql= '插入[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) as bigint),0)'+', isnull(cast(max(TMSTAMP) as bigint),0)'+',count(1)'+', min ( UPDATE_TIME)'+', max(UPDATE_TIME),'+'''' +cast (@datetime as varchar(20))+''''+' from '+@database+'.'+@schema+'.' + @tbname +' 与 (nolock)'--print @sqlEXEC (@sql)endendSET NOCOUNT off-------------------------------- --- ----------------------------------------------------------- --- --------------------------------------- GO

使用 [datayesdb]GO/***** 对象: StoredProcedure [dba].[GetMaxTMstmp_job] 脚本日期: 2017/5/8 14:07:45 **** **/ SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 过程 [dba].[GetMaxTMstmp_job] asdeclare @database varchar(20)、@schema varchar(20)、@tblname varchar(100)、@datetime datetimeSET NOCOUNT onset @datetime=GETDATE()set @database= 'datayesdb'声明 mycursor 游标用于选择 schema_name(schema_id ) [schema],name from sys.tables with (nolock) where type='U' order by [schema],name --open mycursor --从 sys.tables 中取出数据并将其赋值给我们刚刚声明的两个变量 fetch next from mycursor into @schema,@tblname -- 确定游标的状态 -- 0 fetch 语句成功 ---1 fetch 语句失败或行不成功我结果集中 ---2 获取的行不存在 while (@@fetch_status =0) Begin --print @tblname exec dba.GetMaxTMstmp @database,@schema,@tblname,@datetime 从 mycursor 获取下一个数据到 @schema ,@tblname end --关闭游标 close mycursor --取消游标 DEALLOCATE mycursor SET NOCOUNT offGO

感谢您仔细阅读本文。希望小编分享的这篇文章《数据库中tmstamp监控示例代码》对大家有所帮助。也希望大家支持、关注行业信息频道。更多相关知识等待您学习！