数据库中tmstampmonitor的示例代码
这篇文章主要介绍了数据库中tmstamp monitor的示例代码,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。
让客户满意是我们工作的目标,不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户,将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴,公司提供的服务项目有:空间域名、虚拟主机、营销软件、网站建设、硚口网站维护、网站推广。
USE [DBCenter] GO /****** Object: StoredProcedure [dba].[GetRowDiff] Script Date: 2017/5/8 13:06:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO alter procedure [dba].[GetRowDiff] as declare @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 on set @sql='' set @datetime =getdate() truncate table DBCenter..viewTMstamp_diff declare mycursor cursor for select 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 from [DBCenter].[dbo].[viewMaxTMtamp] a with (nolock) where convert(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) ) as aa join ( select b.databse_name,b.[schema_name],b.table_name,b.max_tmstamp,b.row_count,b.record_time from [DBCenter].[dbo].[viewMaxTMtamp] b with (nolock) where convert(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) ) as bb on aa.databse_name=bb.databse_name and aa.[schema_name]=bb.[schema_name] and aa.table_name=bb.table_name --打开游标 open mycursor --从游标里取出数据赋值到我们刚才声明的2个变量中 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 fetch语句失败或此行不在结果集中 ---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]) select '+''''+@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)+''''+' from '+@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='insert into 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]) select '+''''+@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)+''''+' from '+@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) 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 end --关闭游标 close mycursor --撤销游标 DEALLOCATE mycursor SET NOCOUNT off GO
USE [datayesdb] GO /****** Object: StoredProcedure [dba].[GetMaxTMstmp] Script Date: 2017/5/8 14:07:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dba].[GetMaxTMstmp] @database varchar(20), @schema varchar(20), @tbname nvarchar(100), @datetime datetime as declare @Max_TMstmp bigint declare @sql nvarchar(4000) declare @sql2 nvarchar(4000) declare @i int=0 DECLARE @startDate1 DATE; DECLARE @startDate DATETIME; DECLARE @endDate DATETIME ; SET NOCOUNT on SET @startDate1=GETDATE();SELECT @startDate=DATEADD(DAY,-0,@startDate1); SET @endDate=DATEADD(d,+1,CONVERT(DATETIME,@startDate1)); --SELECT @startDate startDate,@endDate endDate; --set @tbname='bond' --print @tbname begin --print @tbname set @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+''''+' and col.name in ('+''''+'TMSTAMP'+''''+','+''''+'UPDATE_TIME'+''''+') and tbl.type='+''''+'U'+''''+ ' and tbl.schema_id =schema_id('+''''+@schema+''''+')' --print @sql2 exec sp_executesql @sql2 ,N'@i int out' ,@i out --print @i if isnull(@i,0)=2 begin set @sql= 'insert into [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 +' with (nolock)' --print @sql EXEC (@sql) end end SET NOCOUNT off ------------------------------------------------------------------------------------------------------------------------- GO
USE [datayesdb] GO /****** Object: StoredProcedure [dba].[GetMaxTMstmp_job] Script Date: 2017/5/8 14:07:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dba].[GetMaxTMstmp_job] as declare @database varchar(20), @schema varchar(20), @tblname varchar(100), @datetime datetime SET NOCOUNT on set @datetime=GETDATE() set @database='datayesdb' declare mycursor cursor for select schema_name(schema_id) [schema],name from sys.tables with (nolock) where type='U' order by [schema],name --打开游标 open mycursor --从游标里取出数据赋值到我们刚才声明的2个变量中 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 fetch next from mycursor into @schema,@tblname end --关闭游标 close mycursor --撤销游标 DEALLOCATE mycursor SET NOCOUNT off GO
感谢你能够认真阅读完这篇文章,希望小编分享的“数据库中tmstamp monitor的示例代码”这篇文章对大家有帮助,同时也希望大家多多支持创新互联,关注创新互联行业资讯频道,更多相关知识等着你来学习!
本文题目:数据库中tmstampmonitor的示例代码
文章起源:http://pwwzsj.com/article/pjsiig.html