点击(此处)折叠或打开
- 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16131092/viewspace-2138382/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16131092/viewspace-2138382/