如何抓取最大的tmstmp

這篇文章主要為大家展示了“如何抓取最大的tmstmp”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“如何抓取最大的tmstmp”這篇文章吧。

alter procedure dba.GetMaxTMstmp 
@tbname nvarchar(100)
as


declare @Max_TMstmp bigint
declare @sql nvarchar(4000)
declare @sql2 nvarchar(4000)
declare @i int=0
--set @tbname='bond'
--print @tbname
begin
--print @tbname
set @sql2='select @i=count(1) from sys.columns col join sys.tables tbl on col.object_id=tbl.object_id where tbl.name='+''''+@tbname+''''+' and col.name='+''''+'TMSTAMP'+''''
exec sp_executesql @sql2  ,N'@i int out' ,@i out
--print @i
if isnull(@i,0)<>0 
begin
set @sql= 'insert into  [DBCenter].[dbo].[viewMaxTMtamp] ([table_name],[max_tmstamp]) select '+''''+@tbname+''''+', isnull(cast(max(TMSTAMP) as bigint),0) from '+@tbname
--print @sql
EXEC (@sql)
end
end
go

==========================================================================================
create procedure dba.GetMaxTMstmp_job 
as
declare @tblname varchar(100)
declare mycursor cursor for select top 10 name from sys.tables where type='U' order by name
 --打開游標  
    open mycursor      
    --從游標里取出數據賦值到我們剛才聲明的2個變量中  
    fetch next from mycursor into @tblname


    --判斷游標的狀態  
    -- 0 fetch語句成功      
    ---1 fetch語句失敗或此行不在結果集中      
    ---2 被提取的行不存在  
    while (@@fetch_status=0)  
    begin   
print @tblname
exec dba.GetMaxTMstmp @tblname
fetch next from mycursor into @tblname
end  
    --關閉游標  
    close mycursor
    --撤銷游標  
    DEALLOCATE mycursor    

==================================================================================================
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'dba.GetMaxTMstmp_job', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=2, 
@notify_level_netsend=2, 
@notify_level_page=2, 
@delete_level=0, 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'dba.GetMaxTMstmp_job', @server_name = N'SH-DATAYESDB'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'dba.GetMaxTMstmp_job', @step_name=N'dba.GetMaxTMstmp_job_daily', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_fail_action=2, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'dba.GetMaxTMstmp_job', 
@database_name=N'datayesdb', 
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'dba.GetMaxTMstmp_job', 
@enabled=1, 
@start_step_id=1, 
@notify_level_eventlog=0, 
@notify_level_email=2, 
@notify_level_netsend=2, 
@notify_level_page=2, 
@delete_level=0, 
@description=N'', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', 
@notify_email_operator_name=N'', 
@notify_netsend_operator_name=N'', 
@notify_page_operator_name=N''
GO

==================================================================================================

USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_id=N'd61c0c64-31bc-4e1c-98e7-140b345f0f48', @name=N'GetMaxTMstmp_job_daily', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=1, 
@freq_subday_interval=0, 
@freq_relative_interval=0, 
@freq_recurrence_factor=1, 
@active_start_date=20170412, 
@active_end_date=99991231, 
@active_start_time=0, 
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

以上是“如何抓取最大的tmstmp”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！