All the jobs dependent on a database

• wannabe dba

  SSCommitted

  Points: 1524

  More actions

  September 14, 2011 at 1:20 pm

  #243695

  SQL Gurus,

  I am in the process of moving databases from SQL 2005 to SQL 2008 R2 and there are a tone of jobs for each of these databases. Do you guys know of an easy way to query the data and get a list of jobs related to a database.

  thanks in advance.

• SkyBVI

  SSCoach

  Points: 15128

  More actions

  September 14, 2011 at 2:12 pm

  #1381608

  go to jobs. select it

  go to object explorer

  select 1

  ctrl all jobs

  right click ...create to file

  then wen u r into sql 2008 r2

  jsut execute tht sql statments

  It should be good to run then.

  Regards,

  Sushant

  Regards
  Sushant Kumar
  MCTS,MCP

• Leo.Miller

  SSChampion

  Points: 13051

  More actions

  September 14, 2011 at 2:36 pm

  #1381617

  This will list all the jobs and their databases assuming the developer was disiplined enough to set the correct database on each job step, and didn't use the database name in the actual step code.

  select j.name,js.database_name from sysjobs j join sysjobsteps js

  on j.job_id = js.job_id

  To go further you would have to search for the databse names in the "command" column of the sysjobsteps table.

  Cheers

  Leo

  Leo
  Nothing in life is ever so complicated that with a little work it can't be made more complicated.

• wannabe dba

  SSCommitted

  Points: 1524

  More actions

  September 14, 2011 at 2:45 pm

  #1381622

  thx leo. that helped a lot .got most of the dependent jobs. there are still some where the wrong databases have been specified ,but this would get me started. It would have been easy if I was moving all the jobs ,but I have to move a select group of db's during each phase of the migration.

• SQLBuddy123

  One Orange Chip

  Points: 28294

  More actions

  September 14, 2011 at 6:47 pm

  #1381694

  Leo posted a very nice script. Sometimes if the Jobs have DTS packages , SSIS packages or Maintenance plans then it becomes difficult to associate the jobs with the database names.

  At times the following queries can be helpful in identifying the jobs with specific DB_Names and DTS and SSIS packages ..

  USE [msdb]

  GO

  SELECT

  j.name,

  js.command,

  FROMdbo.sysjobs j

  JOINdbo.sysjobsteps js

  ONjs.job_id = j.job_id

  WHEREjs.command LIKE N'%DB_NAME%'

  GO

  USE [msdb]

  GO

  SELECT

  j.name,

  js.command,

  FROMdbo.sysjobs j

  JOINdbo.sysjobsteps js

  ONjs.job_id = j.job_id

  WHEREjs.command LIKE N'%DTS%' or js.command LIKE N'%SSIS%'

  GO

  USE [msdb]

  GO

  SELECT

  j.name,

  js.command,

  FROMdbo.sysjobs j

  JOINdbo.sysjobsteps js

  ONjs.job_id = j.job_id

  WHERE js.command LIKE '%DTS%'and subsystem LIKE '%CmdExec%'

  GO

  Thank You,

  Best Regards,

  SQLBuddy