How to extract the scripts for all SCHEDULED stored procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How to extract the scripts for all SCHEDULED...

How to extract the scripts for all SCHEDULED stored procedures

Rate Topic

Display Mode

Topic Options

Author

Message

Philip-918225

Posted Wednesday, April 22, 2009 8:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 12, 2012 6:10 AM
Points: 27, Visits: 81

Dear All,

I am looking for a method to extract the scripts for all SCHEDULED stored procedures in SQL Server 2005?

Thanks,

Philip.

Post #702396

Mayank Khatri

Posted Wednesday, April 22, 2009 8:39 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:04 PM
Points: 1,776, Visits: 1,454

Do u mean All S.P. scheduled to run in ur SQL Server agent?

Post #702399

Philip-918225

Posted Wednesday, April 22, 2009 10:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 12, 2012 6:10 AM
Points: 27, Visits: 81

Yes. That's what I'm looking for.

Thanks,

Philip

Post #702525

Mayank Khatri

Posted Wednesday, April 22, 2009 12:09 PM

SSCommitted

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:04 PM
Points: 1,776, Visits: 1,454

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

Post #702598

Philip-918225

Posted Thursday, April 23, 2009 5:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 12, 2012 6:10 AM
Points: 27, Visits: 81

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

Post #703063

Greg Charles

Posted Thursday, April 23, 2009 9:31 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 4,009, Visits: 4,891

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

Post #703333

« Prev Topic | Next Topic »

Permissions