Need to find all scheduled jobs and SQL they execute for particular 3 tables

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  March 13, 2020 at 2:07 am

  #3733884

  I want to run a query, against MSDB I assume, that will show me all scheduled jobs and sql statements that they execute at each step for particular three tables. Say, Table1, Table1, and Table3. I don't want to see any other info but only on Agent Jobs that use names of one of these three tables in one of the SQL Statements that jobs steps execute.

  Is this possible to achieve? Does anyone know about an existing script that does it?

  Likes to play Chess

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 13, 2020 at 9:08 am

  #3733944

  Query sysjobsteps to get the SQL statements.  Join to sysjobs to get the name of the jobs.

  John

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  March 13, 2020 at 11:53 am

  #3733995

  Ok. Thanks.

  I got it this way:

  SELECT

  job.job_id,

  notify_level_email,

  name,

  enabled,

  description,

  step_name,

  command,

  server,

  database_name

  FROM

  msdb.dbo.sysjobs job

  INNER JOIN

  msdb.dbo.sysjobsteps steps

  ON

  job.job_id = steps.job_id

  WHERE

  sysjobsteps.Command like '%table1%'

  OR

  sysjobsteps.Command like '%table2%'

  Likes to play Chess