Search This Blog

Friday, November 23, 2012

How to find jobs associated to a sql table?

Select sj.* from MSDB.dbo.sysjobsteps ss
Inner Join msdb..sysjobs sj On sj.job_id = ss.job_id
Where ss.command like '%tblUser%'



oR

select sj.*
from MSDB.dbo.sysjobsteps ss
inner join MSDB.dbo.sysjobs sj
on sj.job_id=ss.job_id,
(select so.*
from sysobjects so
inner join syscomments sc
on sc.id=so.id
where so.type='p'
and sc.text like '%tblUser%'
)aa
where ss.command like '%tblUser%'
or
ss.command like '%'+ aa.name +'%'