Procurando uma string em um job (ou step) no SQL Server

Visualizações: 3.765 views

Tempo de Leitura: 2 minutos

Pessoal,
Boa noite.

Hoje vou trazer uma Stored Procedure bem legal pra vocês, que utiliza views de catálogo do banco de dados para procurar uma determinada string contida no título do job ou na linha de comando de algum step do job. Eu tentei formatar a saída para trazer informações bem completas sobre os resultados retornados. Até utilizei uma vez para listar todos os jobs ativos no banco com apenas 1 linha de código.. rs

stpBusca_String_Job:

USE [CLR]
GO

CREATE PROCEDURE [dbo].[stpBusca_String_Job] ( @String VARCHAR(100) )
AS BEGIN

	SELECT
		[sJOB].[name] AS [JobName] ,
		CASE [sJOB].[enabled]
		  WHEN 1 THEN 'Yes'
		  WHEN 0 THEN 'No'
		END AS [IsEnabled] ,
		[sJOB].[date_created] AS [JobCreatedOn] ,
		[sJOB].[date_modified] AS [JobLastModifiedOn] ,
		[sJSTP].[step_id] AS [StepNo] ,
		[sJSTP].[step_name] AS [StepName] ,
		[sDBP].[name] AS [JobOwner] ,
		[sCAT].[name] AS [JobCategory] ,
		[sJOB].[description] AS [JobDescription] ,
		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],
		CASE
			WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
			ELSE 'Yes'
		  END AS [IsScheduled],
		[sSCH].[name] AS [JobScheduleName],
		CASE [sJOB].[delete_level]
			WHEN 0 THEN 'Never'
			WHEN 1 THEN 'On Success'
			WHEN 2 THEN 'On Failure'
			WHEN 3 THEN 'On Completion'
		  END AS [JobDeletionCriterion]
	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]
		LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
		LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
		LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
		LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
	WHERE
		[sJSTP].[command] LIKE '%' + @String + '%'
		OR [sJOB].[name] LIKE '%' + @String + '%'
	ORDER BY
		[JobName] ,
		[StepNo]
		
END

USE [CLR]

CREATE PROCEDURE [dbo].[stpBusca_String_Job] ( @String VARCHAR(100) )

AS BEGIN

SELECT

[sJOB].[name] AS [JobName] ,

CASE [sJOB].[enabled]

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END AS [IsEnabled] ,

[sJOB].[date_created] AS [JobCreatedOn] ,

[sJOB].[date_modified] AS [JobLastModifiedOn] ,

[sJSTP].[step_id] AS [StepNo] ,

[sJSTP].[step_name] AS [StepName] ,

[sDBP].[name] AS [JobOwner] ,

[sCAT].[name] AS [JobCategory] ,

[sJOB].[description] AS [JobDescription] ,

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],

CASE

WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'

ELSE 'Yes'

END AS [IsScheduled],

[sSCH].[name] AS [JobScheduleName],

CASE [sJOB].[delete_level]

WHEN 0 THEN 'Never'

WHEN 1 THEN 'On Success'

WHEN 2 THEN 'On Failure'

WHEN 3 THEN 'On Completion'

END AS [JobDeletionCriterion]

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]

LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]

LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]

LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]

LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]

WHERE

[sJSTP].[command] LIKE '%' + @String + '%'

OR [sJOB].[name] LIKE '%' + @String + '%'

ORDER BY

[JobName] ,

[StepNo]

END

Utilização:

EXEC CLR.dbo.stpBusca_String_Job 'Importa' -- Imprime na tela uma lista com todos os jobs ou steps que contenham a palavra 'Importa' no nome ou código-fonte do step.

EXEC CLR.dbo.stpBusca_String_Job @String = 'Gera' -- Imprime na tela uma lista com todos os jobs ou steps que contenham a palavra 'Gera' no nome ou código-fonte do step.

EXEC CLR.dbo.stpBusca_String_Job 'Importa' -- Imprime na tela uma lista com todos os jobs ou steps que contenham a palavra 'Importa' no nome ou código-fonte do step.

EXEC CLR.dbo.stpBusca_String_Job @String = 'Gera' -- Imprime na tela uma lista com todos os jobs ou steps que contenham a palavra 'Gera' no nome ou código-fonte do step.

É isso aí pessoal,
Bom proveito!

Augusto disse:

12 de maio de 2023 às 13:11

Top d+

Responder
Augusto disse:

12 de maio de 2023 às 13:11

top d +

Responder
Humberto disse:

26 de março de 2021 às 11:21

2021 e esse script de 2014, sendo muito útil ! Obrigado !

Responder

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

Procurando uma string em um job (ou step) no SQL Server

You may also like...

3 Responses

Deixe uma respostaCancelar resposta

You may also like...

SQL Server – Msg 443 Invalid use of a side-effecting operator ‘rand’ within a function

SQL Server – Como criar consultas recursivas com a CTE (Common Table Expressions)

SQL Server – Como resolver o Warning Null value is eliminated by an aggregate or other SET operation

3 Responses

Deixe uma respostaCancelar resposta