Shulou(Shulou.com)05/31 Report--

This article mainly introduces the sample code of tmstamp monitor in the database, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

USE [DBCenter] GO/* Object: StoredProcedure [dba]. [GetRowDiff] Script Date: 13:06:50 on 2017-5-8 * / 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, AAA [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. A.record_time from [DBCenter]. [dbo]. [viewMaxTMtamp] a with (nolock) where convert (varchar (10), a. Recordtimejue 120) +'+ convert (varchar (2), a. Recordtime timeline 114) = convert (varchar (10), dateadd (hh,0,getdate ()), 120) +'+ convert (varchar (2), dateadd (hh,0,getdate () As aa join (select b.databse_name, b. [schema _ name], b.tableaccountmstampreb.Maxchangtmstampreb.rowroomcountbookb.recordtimestamp [DBCenter]. [dbo]. [viewMaxTMtamp] b with (nolock) where convert (varchar (10), b.recordtimestamp120) +'+ convert (varchar (2), b.record_time) ) = convert (varchar (10), getdate (), 120) +'+ convert (varchar (2), dateadd (hh,-1,getdate () As bb on aa.databse_name=bb.databse_name and aa. [schema _ name] = bb. [schema _ name] and aa.table_name=bb.table_name-- Open cursor open mycursor-- fetch data from the cursor and assign it to fetch next from mycursor into @ databse_name,@schema_name,@table_name,@max_tmstamp_2 in the two variables we just declared @ row_count_2,@max_tmstamp_1,@row_count_1,@record_time_2,@record_time_1-- determine the state of the cursor-- 0 fetch statement succeeded-1 fetch statement failed or the row is not in the result set-2 the extracted row does not exist 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) +' 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_2120) +'+','+'+ convert (varchar (19)) @ record_time_1120) +''+','+''+ 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_1 as varchar (100)) + 'and cast (TMSTAMP as bigint)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.