April 22, 2009 at 8:34 am
Dear All,
I am looking for a method to extract the scripts for all SCHEDULED stored procedures in SQL Server 2005?
Thanks,
Philip.
April 22, 2009 at 8:39 am
Do u mean All S.P. scheduled to run in ur SQL Server agent?
April 22, 2009 at 10:34 am
Yes. That's what I'm looking for.
Thanks,
Philip
April 22, 2009 at 12:09 pm
Excuse me for not understanding the problem correctly, if thats the case then why is that u cant just checks the job and which S.P is being executed bu the scheduler?
R there Lots of SP's or Jobs to look at or u dont have access to scheduler.
msdb.dbo.sysjobsteps will give u command executed during any job execution. In that command U will have ur SP. The text of SP can be found out using SP_helptext SPName
April 23, 2009 at 5:59 am
I have around 60 scheduled jobs out of which most of them of type 'TSQL'. I was able to find the text one by one by selecting the name in the same way you've mentioned in the email but was wondering any other options to get the actual text of all scheduled jobs using any menthods (if possible through any sql commands). I was only able to select the 'command' even before posting the msg. I was looking for the text but couldn't find any text fields in any of the msdb tables that' why my questions to you all.
Thanks,
Philip
April 23, 2009 at 9:31 am
Philip,
I'm still a bit uncertain of what you want to script. The definitions of all jobs are in the sysjobs table in msdb. The definitions of stored procedures are in the sys.procedures view in whichever database contains the stored procedure. You can use sp_helptext, as Mayank suggested, to get the definition of a specific stored procedure.
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply