如何抓取最大的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=Ndba.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=Nsa, @job_id = @jobId OUTPUT

select @jobId

GO

EXEC msdb.dbo.sp_add_jobserver @job_name=Ndba.GetMaxTMstmp_job, @server_name = NSH-DATAYESDB

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_jobstep @job_name=Ndba.GetMaxTMstmp_job, @step_name=Ndba.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=NTSQL, 

@command=Ndba.GetMaxTMstmp_job, 

@database_name=Ndatayesdb, 

@flags=0

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_update_job @job_name=Ndba.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=Nsa, 

@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=Nd61c0c64-31bc-4e1c-98e7-140b345f0f48, @name=NGetMaxTMstmp_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”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！