热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

SQLServer查在作业中被使用的存储过程

SELECT[sJOB].[job_id]AS[JobID],[sJOB].[name]AS[JobName],[sJSTP].[step_uid]AS[StepID],[sJST

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 '%PublishBS004%'
ORDER BY [JobName],[StepNo]

 



推荐阅读
author-avatar
mobiledu2502859367
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有