SQL Server - Como buscar uma string no código de SP's que são chamadas por jobs do SQL Agent

Post Views 280 views

Reading time 4 minutes

Olá pessoal,
Tudo bem ?

Nesse post vou compartilhar com vocês uma necessidade que tive na sexta-feira, onde um servidor de arquivos ficaria desligado por algumas horas para manutenção e precisava identificar quais os jobs que acessavam esse servidor e por isso, seriam impactados por essa manutenção, ou seja, buscar uma string no código de SP’s que são chamadas por jobs do SQL Agent.

Um outro exemplo em que isso pode ser bem útil, é para identificar os jobs que acessam uma determinada tabela do banco de dados, caso ela precise sofrer alguma alteração ou manutenção, por exemplo, e você precise dessa informação para avaliar os impactos.

Pois bem, para conseguir automatizar isso, criei uma SP que irá varrer todos os databases da sua instância (utilizando a sp_msforeachdb), lendo a view sys.sql_modules de cada banco. Após identificar todas as SP’s ou funções que possuem a determinada string que você está buscando, a rotina faz um cruzamento dessas informações com as views do SQL Agent para identificar quais jobs que chamam essas SP’s.

Código fonte da SP

Para conseguir realizar essa verificação, basta utilizar o código-fonte abaixo, que também está disponível neste GitHub. Fique à vontade para sugerir alterações e melhorias. Uma delas, é que a SP abaixo funcione quando o job chama uma SP, que chama outra SP e esta sim, possui a string que estamos buscando.

USE [master]
GO

CREATE PROCEDURE [dbo].[stpBusca_String_Rotinas_Job]
    @Ds_Busca [varchar](100)
AS
BEGIN


    IF (OBJECT_ID('tempdb..#Resultado') IS NOT NULL) DROP TABLE #Resultado
    CREATE TABLE #Resultado (
        Ds_Database SYSNAME NULL,
        Ds_Objeto SYSNAME NULL,
        Ds_Schema SYSNAME NULL,
        Ds_Tipo VARCHAR(100) NULL
    )

    
    DECLARE @Query VARCHAR(MAX) = '
    SELECT 
        DB_NAME(DB_ID(''?''))       AS Ds_Database,
        B.name                      AS Ds_Objeto,
        C.name                      AS Ds_Schema,
        B.type_desc                 AS Ds_Tipo
    FROM 
        [?].sys.sql_modules         A   WITH(NOLOCK)
        JOIN [?].sys.objects        B   WITH(NOLOCK)    ON A.object_id = B.object_id
        JOIN [?].sys.schemas        C   WITH(NOLOCK)    ON B.schema_id = C.schema_id
    WHERE
        A.definition LIKE ''%' + @Ds_Busca + '%''
    '


    INSERT INTO #Resultado
    EXEC master.sys.sp_msforeachdb @Query


    SELECT
        C.Ds_Database,
        C.Ds_Schema,
        C.Ds_Objeto,
        A.[name] AS job_name,
        A.[enabled],
        B.step_id,
        B.step_name,
        B.[database_name],
        (CASE WHEN B.last_run_date != 0 THEN msdb.dbo.agent_datetime(B.last_run_date, B.last_run_time) ELSE NULL END) AS last_run,
        REPLACE(REPLACE(REPLACE(B.[command], CHAR(10) + CHAR(13), ' '), CHAR(13), ' '), CHAR(10), ' ') AS [ExecutableCommand],
        E.[name] AS [JobScheduleName],
        CASE 
            WHEN E.[freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
            WHEN E.[freq_type] = 128 THEN 'Start whenever the CPUs become idle'
            WHEN E.[freq_type] IN (4,8,16,32) THEN 'Recurring'
            WHEN E.[freq_type] = 1 THEN 'One Time'
        END [ScheduleType], 
        CASE E.[freq_type]
            WHEN 1 THEN 'One Time'
            WHEN 4 THEN 'Daily'
            WHEN 8 THEN 'Weekly'
            WHEN 16 THEN 'Monthly'
            WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
            WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
            WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence], 
      CASE E.[freq_type]
            WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
            WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' week(s) on '
                    + CASE WHEN E.[freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN E.[freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN E.[freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN E.[freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN E.[freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN E.[freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN E.[freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
            WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST(E.[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
            WHEN 32 THEN 'Occurs on '
                     + CASE E.[freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' ' 
                     + CASE E.[freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST(E.[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        END AS [Recurrence], 
        CASE E.[freq_subday_type]
            WHEN 1 THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            WHEN 2 THEN 'Occurs every ' + CAST(E.[freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            WHEN 4 THEN 'Occurs every ' + CAST(E.[freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            WHEN 8 THEN 'Occurs every ' + CAST(E.[freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        END [Frequency], 
        STUFF(STUFF(CAST(E.[active_start_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageStartDate], 
        STUFF(STUFF(CAST(E.[active_end_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageEndDate]
    FROM 
        msdb.dbo.sysjobs A WITH(NOLOCK)
        JOIN msdb.dbo.sysjobsteps B WITH(NOLOCK) ON A.job_id = B.job_id
        JOIN #Resultado C ON B.command LIKE '%' + C.Ds_Objeto + '%'
        LEFT JOIN [msdb].[dbo].[sysjobschedules] AS D ON [A].[job_id] = D.[job_id]
        LEFT JOIN [msdb].[dbo].[sysschedules] AS E ON D.[schedule_id] = E.[schedule_id]
    WHERE
        C.Ds_Database = B.[database_name]
        OR B.command LIKE '%' + C.Ds_Database + '%'

    
END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

USE [master]

CREATE PROCEDURE [dbo].[stpBusca_String_Rotinas_Job]

@Ds_Busca [varchar](100)

BEGIN

IF (OBJECT_ID('tempdb..#Resultado') IS NOT NULL) DROP TABLE #Resultado

CREATE TABLE #Resultado (

Ds_Database SYSNAME NULL,

Ds_Objeto SYSNAME NULL,

Ds_Schema SYSNAME NULL,

Ds_Tipo VARCHAR(100) NULL

)

DECLARE @Query VARCHAR(MAX) = '

SELECT

DB_NAME(DB_ID(''?'')) AS Ds_Database,

B.name AS Ds_Objeto,

C.name AS Ds_Schema,

B.type_desc AS Ds_Tipo

FROM

[?].sys.sql_modules A WITH(NOLOCK)

JOIN [?].sys.objects B WITH(NOLOCK) ON A.object_id = B.object_id

JOIN [?].sys.schemas C WITH(NOLOCK) ON B.schema_id = C.schema_id

WHERE

A.definition LIKE ''%' + @Ds_Busca + '%''

INSERT INTO #Resultado

EXEC master.sys.sp_msforeachdb @Query

SELECT

C.Ds_Database,

C.Ds_Schema,

C.Ds_Objeto,

A.[name] AS job_name,

A.[enabled],

B.step_id,

B.step_name,

B.[database_name],

(CASE WHEN B.last_run_date != 0 THEN msdb.dbo.agent_datetime(B.last_run_date, B.last_run_time) ELSE NULL END) AS last_run,

REPLACE(REPLACE(REPLACE(B.[command], CHAR(10) + CHAR(13), ' '), CHAR(13), ' '), CHAR(10), ' ') AS [ExecutableCommand],

E.[name] AS [JobScheduleName],

CASE

WHEN E.[freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'

WHEN E.[freq_type] = 128 THEN 'Start whenever the CPUs become idle'

WHEN E.[freq_type] IN (4,8,16,32) THEN 'Recurring'

WHEN E.[freq_type] = 1 THEN 'One Time'

END [ScheduleType],

CASE E.[freq_type]

WHEN 1 THEN 'One Time'

WHEN 4 THEN 'Daily'

WHEN 8 THEN 'Weekly'

WHEN 16 THEN 'Monthly'

WHEN 32 THEN 'Monthly - Relative to Frequency Interval'

WHEN 64 THEN 'Start automatically when SQL Server Agent starts'

WHEN 128 THEN 'Start whenever the CPUs become idle'

END [Occurrence],

CASE E.[freq_type]

WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'

WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' week(s) on '

+ CASE WHEN E.[freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END

+ CASE WHEN E.[freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END

+ CASE WHEN E.[freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END

+ CASE WHEN E.[freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END

+ CASE WHEN E.[freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END

+ CASE WHEN E.[freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END

+ CASE WHEN E.[freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END

WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST(E.[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'

WHEN 32 THEN 'Occurs on '

+ CASE E.[freq_relative_interval]

WHEN 1 THEN 'First'

WHEN 2 THEN 'Second'

WHEN 4 THEN 'Third'

WHEN 8 THEN 'Fourth'

WHEN 16 THEN 'Last'

END

+ ' '

+ CASE E.[freq_interval]

WHEN 1 THEN 'Sunday'

WHEN 2 THEN 'Monday'

WHEN 3 THEN 'Tuesday'

WHEN 4 THEN 'Wednesday'

WHEN 5 THEN 'Thursday'

WHEN 6 THEN 'Friday'

WHEN 7 THEN 'Saturday'

WHEN 8 THEN 'Day'

WHEN 9 THEN 'Weekday'

WHEN 10 THEN 'Weekend day'

END

+ ' of every ' + CAST(E.[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'

END AS [Recurrence],

CASE E.[freq_subday_type]

WHEN 1 THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

WHEN 2 THEN 'Occurs every ' + CAST(E.[freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

WHEN 4 THEN 'Occurs every ' + CAST(E.[freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

WHEN 8 THEN 'Occurs every ' + CAST(E.[freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST(E.[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

END [Frequency],

STUFF(STUFF(CAST(E.[active_start_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageStartDate],

STUFF(STUFF(CAST(E.[active_end_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageEndDate]

FROM

msdb.dbo.sysjobs A WITH(NOLOCK)

JOIN msdb.dbo.sysjobsteps B WITH(NOLOCK) ON A.job_id = B.job_id

JOIN #Resultado C ON B.command LIKE '%' + C.Ds_Objeto + '%'

LEFT JOIN [msdb].[dbo].[sysjobschedules] AS D ON [A].[job_id] = D.[job_id]

LEFT JOIN [msdb].[dbo].[sysschedules] AS E ON D.[schedule_id] = E.[schedule_id]

WHERE

C.Ds_Database = B.[database_name]

OR B.command LIKE '%' + C.Ds_Database + '%'

END

Exemplo de uso:

CREATE PROCEDURE dbo.stpConsulta_Feriado_SP
AS
BEGIN

    SELECT * FROM dirceuresende.dbo.Feriado
    WHERE Sg_UF = 'SP'

END
GO

CREATE PROCEDURE dbo.stpConsulta_Feriado_ES
AS
BEGIN

    SELECT * FROM dirceuresende.dbo.Feriado
    WHERE Sg_UF = 'ES'

END

CREATE PROCEDURE dbo.stpConsulta_Feriado_SP

BEGIN

SELECT * FROM dirceuresende.dbo.Feriado

WHERE Sg_UF = 'SP'

END

CREATE PROCEDURE dbo.stpConsulta_Feriado_ES

BEGIN

SELECT * FROM dirceuresende.dbo.Feriado

WHERE Sg_UF = 'ES'

END

É isso aí, pessoal! Espero que tenham gostado dessa SP, um abraço e até o próximo post.

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Como buscar uma string no código de SP’s que são chamadas por jobs do SQL Agent

Código fonte da SP

Exemplo de uso:

You may also like...

Deixe uma respostaCancelar resposta

Código fonte da SP

Exemplo de uso:

You may also like...

SQL Server – Como listar as maiores tabelas e índices do banco de dados e mostrar o tamanho de cada

Webcast – Comemoração 300 posts + Novidades do SQL Server 2019 (04/10/2018 – 21h)

SQL Server 2016 – Como consultar informações de um CEP utilizando a API Bemean e a função JSON_VALUE

Deixe uma respostaCancelar resposta