注册
登录
agent
如何查找所有调用给定存储过程的SQL Agent作业
返回
如何查找所有调用给定存储过程的SQL Agent作业
作者:
狗头军师
发布时间:
2024-07-17 06:50:38 (1月前)
我正在使用SQL 2008 / R2。我想运行一个查询,以查看是否有一个SQL Agent作业调用指定的存储过程(有太多手动检查)。
收藏
举报
2 条回复
1#
回复此人
v-star*위위
|
2020-08-25 16-17
这是一个查询,将为您提供更多信息(请查看WHERE存储的proc名称的子句): ``` SELECT [sJOB].[job_id] AS [JobID] , [sJOB].[name] AS [JobName] , [sJSTP].[step_uid] AS [StepID] , [sJSTP].[step_id] AS [StepNo] , [sJSTP].[step_name] AS [StepName] , CASE [sJSTP].[subsystem] WHEN 'ActiveScripting' THEN 'ActiveX Script' WHEN 'CmdExec' THEN 'Operating system (CmdExec)' WHEN 'PowerShell' THEN 'PowerShell' WHEN 'Distribution' THEN 'Replication Distributor' WHEN 'Merge' THEN 'Replication Merge' WHEN 'QueueReader' THEN 'Replication Queue Reader' WHEN 'Snapshot' THEN 'Replication Snapshot' WHEN 'LogReader' THEN 'Replication Transaction-Log Reader' WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command' WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query' WHEN 'SSIS' THEN 'SQL Server Integration Services Package' WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)' ELSE sJSTP.subsystem END AS [StepType] , [sPROX].[name] AS [RunAs] , [sJSTP].[database_name] AS [Database] , [sJSTP].[command] AS [ExecutableCommand] , CASE [sJSTP].[on_success_action] WHEN 1 THEN 'Quit the job reporting success' WHEN 2 THEN 'Quit the job reporting failure' WHEN 3 THEN 'Go to the next step' WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) + ' ' + [sOSSTP].[step_name] END AS [OnSuccessAction] , [sJSTP].[retry_attempts] AS [RetryAttempts] , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)] , CASE [sJSTP].[on_fail_action] WHEN 1 THEN 'Quit the job reporting success' WHEN 2 THEN 'Quit the job reporting failure' WHEN 3 THEN 'Go to the next step' WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) + ' ' + [sOFSTP].[step_name] END AS [OnFailureAction] FROM [msdb].[dbo].[sysjobsteps] AS [sJSTP] INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id] AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id] AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id] LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id] WHERE [sJSTP].[command] LIKE '%MyStoredProc%' ORDER BY [JobName], [StepNo] ``` 对于上面的大多数查询,请参考Dattatrey Sindol 的“ 查询SQL Server代理作业信息”一文。
编辑
登录
后才能参与评论