tmstamp monitor

来源：这里教程网时间：2026-03-02 11:12:06 作者：

点击(此处)折叠或打开

USE [DBCenter]

/****** Object: StoredProcedure [dba].[GetRowDiff] Script Date: 2017/5/8 13:06:50 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

alter procedure [dba].[GetRowDiff]

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 ,'

--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

点击(此处)折叠或打开

USE [datayesdb]

/****** Object: StoredProcedure [dba].[GetMaxTMstmp] Script Date: 2017/5/8 14:07:04 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE procedure [dba].[GetMaxTMstmp]

@database varchar(20),

@schema varchar(20),

@tbname nvarchar(100),

@datetime datetime

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+''''+