数据库中tmstamp monitor的示例代码
发表于:2024-10-27 作者:千家信息网编辑
千家信息网最后更新 2024年10月27日,这篇文章主要介绍了数据库中tmstamp monitor的示例代码,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。USE [DBCent
千家信息网最后更新 2024年10月27日数据库中tmstamp monitor的示例代码
这篇文章主要介绍了数据库中tmstamp monitor的示例代码,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。
USE [DBCenter]GO/****** Object: StoredProcedure [dba].[GetRowDiff] Script Date: 2017/5/8 13:06:50 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOalter procedure [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 onset @sql=''set @datetime =getdate()truncate table DBCenter..viewTMstamp_diffdeclare 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 offGO
USE [datayesdb]GO/****** Object: StoredProcedure [dba].[GetMaxTMstmp] Script Date: 2017/5/8 14:07:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [dba].[GetMaxTMstmp] @database varchar(20),@schema varchar(20),@tbname nvarchar(100),@datetime datetimeasdeclare @Max_TMstmp bigintdeclare @sql nvarchar(4000)declare @sql2 nvarchar(4000)declare @i int=0DECLARE @startDate1 DATE;DECLARE @startDate DATETIME;DECLARE @endDate DATETIME ;SET NOCOUNT onSET @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 @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+''''+' and col.name in ('+''''+'TMSTAMP'+''''+','+''''+'UPDATE_TIME'+''''+') and tbl.type='+''''+'U'+''''+ ' and tbl.schema_id =schema_id('+''''+@schema+''''+')' --print @sql2exec sp_executesql @sql2 ,N'@i int out' ,@i out--print @iif isnull(@i,0)=2beginset @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 @sqlEXEC (@sql)endendSET NOCOUNT off------------------------------------------------------------------------------------------------------------------------- GO
USE [datayesdb]GO/****** Object: StoredProcedure [dba].[GetMaxTMstmp_job] Script Date: 2017/5/8 14:07:45 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [dba].[GetMaxTMstmp_job] asdeclare @database varchar(20),@schema varchar(20),@tblname varchar(100),@datetime datetimeSET NOCOUNT onset @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 offGO
感谢你能够认真阅读完这篇文章,希望小编分享的"数据库中tmstamp monitor的示例代码"这篇文章对大家有帮助,同时也希望大家多多支持,关注行业资讯频道,更多相关知识等着你来学习!
游标
数据
篇文章
语句
代码
数据库
示例
成功
变量
状态
结果
价值
兴趣
同时
更多
朋友
知识
编带
行业
资讯
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
大学生毕业论文网络安全的
普陀区服务器回收推荐厂家
python 元组插入数据库
最近国家网络安全专项整治
三星服务器遭到黑客攻击
上海星彧互联网科技有限公司
5g软件开发上市公司
国泰安数据库值怎么找
数据库的多表查询
广州办公系统软件开发哪家便宜
微信直播软件开发
服务器需要分区
数据库中时间的表达
软件开发和销售相结合的岗位
sql数据库表建立索引
数据库对主键的说法
专业软件开发培训班
瀚高数据库免费版下载
固安丰汇网络技术
网络安全管理条例草案
专业网络安全方案设计费用
ios 数据库 封装
线上承接软件开发
润和软件开发套件是什么意思
计算机网络安全管理设置
如何增加wi-fi网络安全性
湖北省网络安全应急平台
k3数据库领料单删除记账标志
网络安全行业战略分析
网络安全法是国家哪个部门