February 25, 2019 at 11:09 am
I am trying to find all stored procedures called by jobs. I have modified
SELECT DISTINCT SPROC=SPECIFIC_NAME
FROM [AdventureWorks2008].[INFORMATION_SCHEMA].[ROUTINES]
CROSS JOIN msdb.dbo.sysjobsteps
WHERE Command like ('%'+SPECIFIC_NAME+'%')
But it isn't returning them properly, any advice would be greatly appreciated.
February 25, 2019 at 11:39 am
And just to be clear I'm not using AdventureWorks, I am using my database. Accounts
February 25, 2019 at 12:19 pm
cbrammer1219 - Monday, February 25, 2019 11:09 AMI am trying to find all stored procedures called by jobs. I have modified
SELECT DISTINCT SPROC=SPECIFIC_NAME
FROM [AdventureWorks2008].[INFORMATION_SCHEMA].[ROUTINES]
CROSS JOIN msdb.dbo.sysjobsteps
WHERE Command like ('%'+SPECIFIC_NAME+'%')But it isn't returning them properly, any advice would be greatly appreciated.
Are you sure there stored procedures in the database being used by jobs?
I just tested it a little and the sql seems to work fine. Did you change it to use the correct database in the from clause? You could try testing it by changing it to check for any msdb procedures, functions, being used in jobs. The default syspolicy_purge_history job would have a couple of rows returned if you did the change to
FROM [msdb].[INFORMATION_SCHEMA].[ROUTINES]
Sue
February 25, 2019 at 12:49 pm
I am definitely sure, I am trying to get the jobs that call the stored procedures, here is how I am trying to get the job and the stored procedure(s) it is using.
select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id inner join sys.objects sp on sd.referencing_id = sp.object_id and sp.type in ('P', 'FN') where o.name = 'YourTableName' order by sp.Name
February 25, 2019 at 1:03 pm
cbrammer1219 - Monday, February 25, 2019 12:49 PMI am definitely sure, I am trying to get the jobs that call the stored procedures, here is how I am trying to get the job and the stored procedure(s) it is using.
select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id inner join sys.objects sp on sd.referencing_id = sp.object_id and sp.type in ('P', 'FN') where o.name = 'YourTableName' order by sp.Name
That's nothing like the original query you posted and it won't work to get jobs that call stored procedures.
You'd have to include the sysjobsteps command since that would have whatever the jobs are calling. This is looking for something completely different.
Sue
February 25, 2019 at 1:14 pm
I am getting the command from sysjobsteps (p.command), I am just trying to parse the command to get the specific ones I'm looking for, which some of them seem to be calling several hundred stored procedures, which I am positive isn't truly happening.
February 25, 2019 at 1:18 pm
Download SQL Search from RedGate.
https://www.red-gate.com/products/sql-development/sql-search/index
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 25, 2019 at 1:25 pm
cbrammer1219 - Monday, February 25, 2019 1:14 PMI am getting the command from sysjobsteps (p.command), I am just trying to parse the command to get the specific ones I'm looking for, which some of them seem to be calling several hundred stored procedures, which I am positive isn't truly happening.
Maybe you posted the wrong thing. You posted that you use this to get the jobs that call stored procedures : select distinct [Table Name] = o.Name,
[Found In] = sp.Name,
sp.type_desc
from sys.objects o
inner join sys.sql_expression_dependencies sd
on o.object_id = sd.referenced_id
inner join sys.objects sp
on sd.referencing_id = sp.object_id
and sp.type in ('P', 'FN')
where o.name = 'YourTableName' order by sp.Name
Sue
February 25, 2019 at 1:34 pm
Yes sorry..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply