How to run all enabled SQL Server Jobs with T-Sql

I’d really recommend not running the output of this script on a live environment! This is just a little something I whipped up to test changes on a development environment.

Running the script will output the T-Sql required to run all jobs on the SQL Server instance. Just copy the text and paste into a new SSMS window and execute. You could change the PRINT to EXEC but I wouldn’t recommend it.

SET NOCOUNT ON

DECLARE @Job TABLE (JobName SYSNAME)
DECLARE @JobName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @JobName = ''

INSERT INTO @Job (JobName)
SELECT j.NAME
FROM msdb.dbo.sysjobs AS j
WHERE j.[enabled] = 1
ORDER BY j.NAME ASC

WHILE @JobName IS NOT NULL
BEGIN
	SET @JobName = (
			SELECT MIN(JobName)
			FROM @Job
			WHERE JobName > @JobName
			)
	SET @Sql = '
EXEC msdb.dbo.sp_start_job @job_name = ' + '''' + @JobName + '''' + '; 
'
	PRINT @Sql
END
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s