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

Visualizações: 281 views

Tempo de Leitura: 4 minutos

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.

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

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...

Como descobrir a data do último acesso a uma tabela ou view e execução da uma procedure no SQL Server

SQL Server – Criptografando senhas com as funções de criptografia simétrica ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE

SQL Server – Como ativar/habilitar o usuário sa

Deixe uma respostaCancelar resposta