Need to get the StoredProcs\Queries used by the sql Jobs in server

  • pols

    SSC Eights!

    Points: 861

    Hi Friends,

    Get the list of stored procs or queries used by the jobs exist in server(please note no need to get the list of stored procs exist in each database,I need the stored procs used only by the jobs in Server)

    Please help me to get the sql sps or commands used ny the jobs.

    Thanks

    Jyothi

  • Nabha

    SSCrazy Eights

    Points: 8534

    Interview huh?

    USE MSDB

    Select SJS.command

    from msdb.dbo.sysjobs SJ

    JOIN sysjobsteps SJS ON SJ.job_id = SJS.job_id

    WHERE SJ.Enabled = 1

    Order by SJ.NAME DESC

    ---------------------------------------------------------------------------------

  • pols

    SSC Eights!

    Points: 861

    No this is not an interview question.:)

    This is my TL requirement.:(

    Anyhow thanks for your reply.

    Another way to get :

    SELECT DISTINCT SPROC=SPECIFIC_NAME

    FROM [msdb].[INFORMATION_SCHEMA].[ROUTINES]

    CROSS JOIN msdb.dbo.sysjobsteps

    WHERE Command like ('%'+SPECIFIC_NAME+'%')

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply