How to know where all my procedure called?

• mamidimahesh-127681

  SSC Eights!

  Points: 868

  More actions

  July 17, 2006 at 12:29 am

  #153089

  Hi,

  I have a procedure which is used in multiple areas like Jobs, Stored procedures etc.,  If I have to know where all my procedure is called, how do I know that?

  Thanks in advance,

   Mahesh

• btate

  SSC Journeyman

  Points: 93

  More actions

  July 17, 2006 at 11:08 am

  #649623

  You might try sp_grep.  Look at http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1201

• Anthony Young-223234

  Old Hand

  Points: 343

  More actions

  July 18, 2006 at 8:34 am

  #649800

  --to find objects (procedures, etc) which reference your procedure:

  select sysobjects.name

  from sysobjects

  inner join syscomments on sysobjects.id = syscomments.id

  where syscomments.text like '%mysp_name%'

  --to find sql agent jobs which reference your procedure:

  select sysjobs.name, sysjobsteps.step_name

  from msdb.dbo.sysjobs sysjobs

  inner join msdb.dbo.sysjobsteps sysjobsteps on sysjobs.job_id = sysjobsteps.job_id

  where sysjobsteps.command like '%mysp_name%'