To execute:
exec spJob_Dependencies
Output:
Job Name Type_Desc
Test_Load Addresses USER_TABLE
Test_Load Post_Codes USER_TABLE
create procedure spJob_Dependencies
as
select
database_name,
Job,
Name,
Type_Desc,
1 as Has_Dep
from
(
Select a.database_name,
a.name Job,
b.name,
b.type_Desc,
charindex(b.name,a.command)as Has_Dep
from
(select js.database_name,
j.name,
js.command
from
msdb..sysjobs j
inner join msdb..sysjobsteps js
on j.job_id = js.job_id
and js.database_name = DB_NAME()
)a
,(select name,
type_desc
from sys.objects
where type IN ('V','U','S','FN')
)b)z
where Has_Dep > 0
order by 1,3,2
exec spJob_Dependencies
Output:
Job Name Type_Desc
Test_Load Addresses USER_TABLE
Test_Load Post_Codes USER_TABLE
create procedure spJob_Dependencies
as
select
database_name,
Job,
Name,
Type_Desc,
1 as Has_Dep
from
(
Select a.database_name,
a.name Job,
b.name,
b.type_Desc,
charindex(b.name,a.command)as Has_Dep
from
(select js.database_name,
j.name,
js.command
from
msdb..sysjobs j
inner join msdb..sysjobsteps js
on j.job_id = js.job_id
and js.database_name = DB_NAME()
)a
,(select name,
type_desc
from sys.objects
where type IN ('V','U','S','FN')
)b)z
where Has_Dep > 0
order by 1,3,2
No comments:
Post a Comment