use msdb
go

create proc sp_scheduledTasks as
/***************************************************************************************************
	Procedure: 	sp_scheduledTasks
	Description:	Generates list of active scheduled tasks, frequency, start & stop times
			from MSDB database
***************************************************************************************************/	
declare @today varchar(30),@server varchar(40),@title varchar(70)
set nocount on
select @title =  replicate(" ",40) + "Active Scheduled Tasks"
print @title
print ""

select @server = "Server " + @@servername
print @server 
print ""

select	-- database name
	DB = convert(char(20),databasename), 
	-- task description
	Task = convert(char(50),name),

	-- scheduled frequency
	'Frequency' = 
	case freqtype
		when 1 then   'One Time'
		when 2 then   'On Demand'
		when 4 then   'Daily'
		when 8 then   'Weekly'
		when 16 then  'Unknown'
		when 32 then 'Monthly'
	end,
	-- Active on following days
	Sun = case
		when (freqinterval & 1 = 1 and freqtype = 8)  or freqtype = 4
			then 'Y' else 'N' end,
	Mon = case
		when (freqinterval & 2 = 2 and freqtype = 8) or freqtype = 4
			then 'Y' else 'N' end,
	Tues = case
		when (freqinterval & 4  = 4 and freqtype = 8) or freqtype = 4
			then 'Y' else 'N' end,	
	Wed = case
		when (freqinterval & 8 = 8and freqtype = 8)  or freqtype = 4
			then 'Y' else 'N' end,
	Thu = case
		when (freqinterval & 16  = 16 and freqtype = 8) or freqtype = 4
			then 'Y' else 'N' end,
	Fri = case
		when (freqinterval & 32 = 32 and freqtype = 8)  or freqtype = 4
			then 'Y' else 'N' end,
	Sat = case
		when (freqinterval & 64) = 64 or freqtype = 4
			then 'Y' else 'N' end,
	-- start time
	Start = substring(
	replicate('0',6-datalength(convert(varchar(6),activestarttimeofday))) +
		convert(varchar(6),activestarttimeofday),1,2) + ":" + 
		 substring(
		replicate('0',6-datalength(convert(varchar(6),activestarttimeofday))) +
		convert(varchar(6),activestarttimeofday),3,2),
	--end time
	'End' = substring(
	replicate('0',6-datalength(convert(varchar(6),activeendtimeofday))) +
		convert(varchar(6),activeendtimeofday),1,2) + ":" + 
		 substring(
		replicate('0',6-datalength(convert(varchar(6),activeendtimeofday))) +
		convert(varchar(6),activeendtimeofday),3,2)	
from systasks 
where enabled = 1
order by databasename,activestarttimeofday


select @today = "Printed " + convert(varchar(30),getdate(),103)

print ""
print ""

print  @today
GO

use master
go
CREATE PROCEDURE scheduleTaskLog AS

/***************************************************************************************************
	Procedure:		scheduleTaskLog
	Description:	Calls the above procedure (via scheduler) to call above sp.
					By using ISQL, the output of sp_scheduledTasks can be output
					to a text file
***************************************************************************************************/	

declare @day char(1),
		@filename varchar(50),
		@cmd varchar(255),
		@serverName varchar(40),
		@path varchar(255)

-- day number used as output file suffix (historicals file for each day of week)

select @day = convert(char(1),datepart(dw,getdate()))
select @filename = 'scheduled tasks.' + @day

-- *** Alter this to re-direct output to another location ***
select @path = '\\' + @@servername + '\c$\mssql\log\'

-- Create SQL String to execute ISQL command

-- *** Alter password***
select @cmd = "isql /Q msdb..sp_scheduledTasks /S" + @servername + " /Usa /P<password> /w 400 > " + 
	'"' + @path + @fileName + '"'

select @cmd

--execute command
exec master..xp_cmdshell @cmd

/*  to copy to backup server - UNCOMMENT THIS BLOCK!!! 
	-- *** Alter server name, path as appropriate ***
	select @cmd = '''copy ' + '"' + @path + @filename + '" ' + '"\\<server name>\c$\mssql\log\live ' + @filename + '"'''
	exec ('master..xp_cmdshell' +  @cmd)
*/

GO