Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 547 other subscribers

Blog Views

1.789.868 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • June 14st, 2014
  • 0
  • Database DMVs and Catalog Views Maintenance SQL Server SQL Server Agent

Searching for a string in a job (or step) in SQL Server

Views: 1.543
Reading Time: 2 minutes

Staff,
Good evening.

Today I will bring you a very cool Stored Procedure, which uses database catalog views to look for a particular string contained in the job title or command line of some job step. I tried to format the output to provide very complete information about the returned results. I even used it once to list all active jobs in the database with just 1 line of code .. rs

stpSearch_String_Job:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
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:

Transact-SQL
1
2
3
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.

That's it guys,
Enjoy it!

tags: searchcommandinformationjobjobslistlistingattorneyqueryscheduleserversqlstepstringtext

You may also like ...

  • SQL Server - Encrypting passwords with the symmetric encryption functions ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE

  • [Video] - Introduction to SQLCLR

  • SQL Server - Permissions for Using OLE Automation (Yes, Documentation is Wrong)

  • Next Enabling OLE Automation via T-SQL on SQL Server
  • Previous SQL Server - Breaking strings into sub-strings using a separator (Split string)

Leave a Comment Cancel reply

Dirceu Resende © 2021. All Rights Reserved.