sql-server-2008 – 如何查找调用给定存储过程的所有SQL代理作业

前端之家收集整理的这篇文章主要介绍了sql-server-2008 – 如何查找调用给定存储过程的所有SQL代理作业前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我在sql 2008 / R2中.我想运行一个查询,看看是否有一个sql代理作业调用指定的存储过程(手动检查太多了).

解决方法

这是一个查询,它将为您提供更多(查看存储的proc名称的WHERE子句):
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转到第Querying SQL Server Agent Job Information条.

原文链接:https://www.f2er.com/mssql/84100.html

猜你在找的MsSQL相关文章