Stored Procedure Documents Scheduled Jobs in SQL Server

If you’re a DBA who has just transferred to a new environment, you should familiarize yourself with the existing SQL Server scheduled jobs. Documenting those job schedules can be an arduous task, especially in a large production environment. Although you can use SQL Server Management Studio (SSMS) to quickly create jobs and schedules, generating a master list usually involves a lot of mouse-clicking followed by a lot of cutting and pasting.

The sp_ShowJobSchedules stored procedure changes all that. The sp_ShowJobSchedules stored procedure generates a master schedule for all jobs on the server. The report generated includes information such as the server name,the job name, the schedule name, whether or not the job is enabled, the frequency, and the interval. For readability, the information is provided in plain English where possible. Figure 3 shows some sample output that has been condensed for space purposes.

I wrote sp_ShowJobSchedules for SQL Server 2005. You can download this store procedure by going to the "Download the Code" icon at the top of the page. I use sp_Show- JobSchedules all the time. I hope you will, too.