Find all Date between a range of dates

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetPeriodsBetweenTwoPeriods]
(
@StartDate Date,
@EndDate Date
)
RETURNS @Periods TABLE (
    Period Date
)
AS
BEGIN
  --select * From dbo.GetPeriodsBetweenTwoPeriods('2012-01-01','2013-01-01')
  WITH CTE
    AS (SELECT
        @StartDate AS Period
    UNION ALL
    SELECT
        DATEADD(MONTH, 1, Period)
    FROM CTE
    WHERE DATEADD(MONTH, 1, Period) <= @EndDate)
    Insert into @Periods SELECT
        *
    FROM CTE
    ORDER BY period DESC

    RETURN

END

Generate Insert Statement of a table

USE [pOrbisNAFTA_LIVE]
GO
/****** Object:  StoredProcedure [dbo].[Damco_GenerateInserts]    Script Date: 2015-10-14 6:53:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Damco_GenerateInserts]    
(    
 @table_name varchar(776),    -- The table/view for which the INSERT statements will be generated using the existing data    
 @target_table varchar(776) = NULL,  -- Use this parameter to specify a different table name into which the data will be inserted    
 @include_column_list bit = 1,  -- Use this parameter to include/ommit column list in the generated INSERT statement    
 @from varchar(800) = NULL,   -- Use this parameter to filter the rows based on a filter condition (using WHERE)    
 @include_timestamp bit = 0,   -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement    
 @debug_mode bit = 0,   -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination    
 @owner varchar(64) = NULL,  -- Use this parameter if you are not the owner of the table    
 @ommit_images bit = 0,   -- Use this parameter to generate INSERT statements by omitting the 'image' columns    
 @ommit_identity bit = 0,  -- Use this parameter to ommit the identity columns    
 @top int = NULL,   -- Use this parameter to generate INSERT statements only for the TOP n rows    
 @cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement    
 @cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement    
 @disable_constraints bit = 0,  -- When 1, disables foreign key constraints and enables them after the INSERT statements    
 @ommit_computed_cols bit = 0  -- When 1, computed columns will not be included in the INSERT statement    
     
)    
AS    
BEGIN    
     
/***********************************************************************************************************    
Procedure: sp_generate_inserts  (Build 22)      
  (Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.)    
                                               
Purpose: To generate INSERT statements from existing data.      
  These INSERTS can be executed to regenerate the data at some other location.    
  This procedure is also useful to create a database setup, where in you can      
  script your data along with your table definitions.    
     
Written by: Narayana Vyas Kondreddi    
         http://vyaskn.tripod.com    
     
Acknowledgements:    
  Divya Kalra -- For beta testing    
  Mark Charsley -- For reporting a problem with scripting uniqueidentifier columns with NULL values    
  Artur Zeygman -- For helping me simplify a bit of code for handling non-dbo owned tables    
  Joris Laperre   -- For reporting a regression bug in handling text/ntext columns    
     
Tested on:  SQL Server 7.0 and SQL Server 2000    
     
Date created: January 17th 2001 21:52 GMT    
     
Date modified: May 1st 2002 19:50 GMT    
     
Email:   vyaskn@hotmail.com    
     
NOTE:  This procedure may not work with tables with too many columns.    
  Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types    
  Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results    
  IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed    
  you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts    
  like nchar and nvarchar    
       
     
Example 1: To generate INSERT statements for table 'titles':    
       
  EXEC sp_generate_inserts 'titles'    
     
Example 2:  To ommit the column list in the INSERT statement: (Column list is included by default)    
  IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,    
  to avoid erroneous results    
       
  EXEC sp_generate_inserts 'titles', @include_column_list = 0    
     
Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:    
     
  EXEC sp_generate_inserts 'titles', 'titlesCopy'    
     
Example 4: To generate INSERT statements for 'titles' table for only those titles      
  which contain the word 'Computer' in them:    
  NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter    
     
  EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"    
     
Example 5:  To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:    
  (By default TIMESTAMP column's data is not scripted)    
     
  EXEC sp_generate_inserts 'titles', @include_timestamp = 1    
     
Example 6: To print the debug information:    
       
  EXEC sp_generate_inserts 'titles', @debug_mode = 1    
     
Example 7:  If you are not the owner of the table, use @owner parameter to specify the owner name    
  To use this option, you must have SELECT permissions on that table    
     
  EXEC sp_generate_inserts Nickstable, @owner = 'Nick'    
     
Example 8:  To generate INSERT statements for the rest of the columns excluding images    
  When using this otion, DO NOT set @include_column_list parameter to 0.    
     
  EXEC sp_generate_inserts imgtable, @ommit_images = 1    
     
Example 9:  To generate INSERT statements excluding (ommiting) IDENTITY columns:    
  (By default IDENTITY columns are included in the INSERT statement)    
     
  EXEC sp_generate_inserts mytable, @ommit_identity = 1    
     
Example 10:  To generate INSERT statements for the TOP 10 rows in the table:    
       
  EXEC sp_generate_inserts mytable, @top = 10    
     
Example 11:  To generate INSERT statements with only those columns you want:    
       
  EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"    
     
Example 12:  To generate INSERT statements by omitting certain columns:    
       
  EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"    
     
Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:    
       
  EXEC sp_generate_inserts titles, @disable_constraints = 1    
     
Example 14:  To exclude computed columns from the INSERT statement:    
  EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1    
***********************************************************************************************************/    
     
SET NOCOUNT ON    
     
--Making sure user only uses either @cols_to_include or @cols_to_exclude    
IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))    
 BEGIN    
  RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)    
  RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified    
 END    
     
--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format    
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))    
 BEGIN    
  RAISERROR('Invalid use of @cols_to_include property',16,1)    
  PRINT 'Specify column names surrounded by single quotes and separated by commas'    
  PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'    
  RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property    
 END    
     
IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))    
 BEGIN    
  RAISERROR('Invalid use of @cols_to_exclude property',16,1)    
  PRINT 'Specify column names surrounded by single quotes and separated by commas'    
  PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'    
  RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property    
 END    
     
     
--Checking to see if the database name is specified along wih the table name    
--Your database context should be local to the table for which you want to generate INSERT statements    
--specifying the database name is not allowed    
IF (PARSENAME(@table_name,3)) IS NOT NULL    
 BEGIN    
  RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)    
  RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed    
 END    
     
--Checking for the existence of 'user table' or 'view'    
--This procedure is not written to work on system tables    
--To script the data in system tables, just create a view on the system tables and script the view instead    
     
IF @owner IS NULL    
 BEGIN    
  IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))      
   BEGIN    
    RAISERROR('User table or view not found.',16,1)    
    PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'    
    PRINT 'Make sure you have SELECT permission on that table or view.'    
    RETURN -1 --Failure. Reason: There is no user table or view with this name    
   END    
 END    
ELSE    
 BEGIN    
  IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)    
   BEGIN    
    RAISERROR('User table or view not found.',16,1)    
    PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'    
    PRINT 'Make sure you have SELECT permission on that table or view.'    
    RETURN -1 --Failure. Reason: There is no user table or view with this name      
   END    
 END    
     
--Variable declarations    
DECLARE  @Column_ID int,        
  @Column_List varchar(8000),      
  @Column_Name varchar(128),      
  @Start_Insert varchar(786),      
  @Data_Type varchar(128),      
  @Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements    
  @IDN varchar(128)  --Will contain the IDENTITY column's name in the table    
     
--Variable Initialization    
SET @IDN = ''    
SET @Column_ID = 0    
SET @Column_Name = ''    
SET @Column_List = ''    
SET @Actual_Values = ''    
     
IF @owner IS NULL      
 BEGIN    
  SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'      
 END    
ELSE    
 BEGIN    
  SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'        
 END    
     
     
--To get the first column's ID    
     
SELECT @Column_ID = MIN(ORDINAL_POSITION)      
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)      
WHERE  TABLE_NAME = @table_name AND    
(@owner IS NULL OR TABLE_SCHEMA = @owner)    
     
     
     
--Loop through all the columns of the table, to get the column names and their data types    
WHILE @Column_ID IS NOT NULL    
 BEGIN    
  SELECT  @Column_Name = QUOTENAME(COLUMN_NAME),      
  @Data_Type = DATA_TYPE      
  FROM  INFORMATION_SCHEMA.COLUMNS (NOLOCK)      
  WHERE  ORDINAL_POSITION = @Column_ID AND      
  TABLE_NAME = @table_name AND    
  (@owner IS NULL OR TABLE_SCHEMA = @owner)    
     
     
     
  IF @cols_to_include IS NOT NULL --Selecting only user specified columns    
  BEGIN    
   IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0      
   BEGIN    
    GOTO SKIP_LOOP    
   END    
  END    
     
  IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns    
  BEGIN    
   IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0      
   BEGIN    
    GOTO SKIP_LOOP    
   END    
  END    
 
  --Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column    
  IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1      
  BEGIN    
   IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column    
    SET @IDN = @Column_Name    
   ELSE    
    GOTO SKIP_LOOP        
  END    
       
  --Making sure whether to output computed columns or not    
  IF @ommit_computed_cols = 1    
  BEGIN    
   IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1      
   BEGIN    
    GOTO SKIP_LOOP          
   END    
  END    
       
  --Tables with columns of IMAGE data type are not supported for obvious reasons    
  IF(@Data_Type in ('image'))    
   BEGIN    
    IF (@ommit_images = 0)    
     BEGIN    
      RAISERROR('Tables with image columns are not supported.',16,1)    
      PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'    
      PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'    
      RETURN -1 --Failure. Reason: There is a column with image data type    
     END    
    ELSE    
     BEGIN    
     GOTO SKIP_LOOP    
     END    
   END    
     
  --Determining the data type of the column and depending on the data type, the VALUES part of    
  --the INSERT statement is generated. Care is taken to handle columns with NULL values. Also    
  --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns    
  SET @Actual_Values = @Actual_Values  +    
  CASE      
   WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')      
    THEN      
     'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'    
   WHEN @Data_Type IN ('datetime','smalldatetime')      
    THEN      
     'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'    
   WHEN @Data_Type IN ('uniqueidentifier')      
    THEN      
     'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'    
   WHEN @Data_Type IN ('text','ntext')      
    THEN      
     'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'          
   WHEN @Data_Type IN ('binary','varbinary')      
    THEN      
     'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'      
   WHEN @Data_Type IN ('timestamp','rowversion')      
    THEN      
     CASE      
      WHEN @include_timestamp = 0      
       THEN      
        '''DEFAULT'''      
       ELSE      
        'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'      
     END    
   WHEN @Data_Type IN ('float','real','money','smallmoney')    
    THEN    
     'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ',2)' + ')),''NULL'')'      
   ELSE      
    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ')' + ')),''NULL'')'      
  END   + '+' +  ''',''' + ' + '    
       
  --Generating the column list for the INSERT statement    
  SET @Column_List = @Column_List +  @Column_Name + ','      
     
  SKIP_LOOP: --The label used in GOTO    
     
  SELECT  @Column_ID = MIN(ORDINAL_POSITION)      
  FROM  INFORMATION_SCHEMA.COLUMNS (NOLOCK)      
  WHERE  TABLE_NAME = @table_name AND      
  ORDINAL_POSITION > @Column_ID AND    
  (@owner IS NULL OR TABLE_SCHEMA = @owner)    
     
     
 --Loop ends here!    
 END    
     
--To get rid of the extra characters that got concatenated during the last run through the loop    
SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)    
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)    
     
IF LTRIM(@Column_List) = ''      
 BEGIN    
  RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)    
  RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter    
 END    
     
--Forming the final string that will be executed, to output the INSERT statements    
IF (@include_column_list <> 0)    
 BEGIN    
  SET @Actual_Values =      
   'SELECT ' +      
   CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +      
'''' + RTRIM(@Start_Insert) +      
   ' ''+' + '''(' + RTRIM(@Column_List) +  '''+' + ''')''' +      
   ' +''VALUES(''+ ' +  @Actual_Values  + '+'')''' + ' ' +      
   COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')    
 END    
ELSE IF (@include_column_list = 0)    
 BEGIN    
  SET @Actual_Values =      
   'SELECT ' +      
   CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +      
   '''' + RTRIM(@Start_Insert) +      
   ' '' +''VALUES(''+ ' +  @Actual_Values + '+'')''' + ' ' +      
   COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')    
 END      
     
--Determining whether to ouput any debug information    
IF @debug_mode =1    
 BEGIN    
  PRINT '/*****START OF DEBUG INFORMATION*****'    
  PRINT 'Beginning of the INSERT statement:'    
  PRINT @Start_Insert    
  PRINT ''    
  PRINT 'The column list:'    
  PRINT @Column_List    
  PRINT ''    
  PRINT 'The SELECT statement executed to generate the INSERTs'    
  PRINT @Actual_Values    
  PRINT ''    
  PRINT '*****END OF DEBUG INFORMATION*****/'    
  PRINT ''    
 END    
       
PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'    
PRINT '--Build number: 22'    
PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'    
PRINT '--http://vyaskn.tripod.com'    
PRINT ''    
PRINT 'SET NOCOUNT ON'    
PRINT ''    
     
     
--Determining whether to print IDENTITY_INSERT or not    
IF (@IDN <> '')    
 BEGIN    
  PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'    
  PRINT 'GO'    
  PRINT ''    
 END    
     
     
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)    
 BEGIN    
  IF @owner IS NULL    
   BEGIN    
    SELECT  'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'    
   END    
  ELSE    
   BEGIN    
    SELECT  'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'    
   END    
     
  PRINT 'GO'    
 END    
     
PRINT ''    
PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''    
     
     
--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!    
EXEC (@Actual_Values)    
     
PRINT 'PRINT ''Done'''    
PRINT ''    
     
     
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)    
 BEGIN    
  IF @owner IS NULL    
   BEGIN    
    SELECT  'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL'  AS '--Code to enable the previously disabled constraints'    
   END    
  ELSE    
   BEGIN    
    SELECT  'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'    
   END    
     
  PRINT 'GO'    
 END    
     
PRINT ''    
IF (@IDN <> '')    
 BEGIN    
  PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'    
  PRINT 'GO'    
 END    
     
PRINT 'SET NOCOUNT OFF'    
     
     
SET NOCOUNT OFF    
RETURN 0 --Success. We are done!    
END  

Find the name of all jobs and schedules

http://www.sqlservercentral.com/Forums/Topic410557-116-1.aspx


/*******************************************************************************

Name: GetJobSchedule (For SQL Server7.0&2000)

Author: M.Pearson
Creation Date: 5 Jun 2002
Version: 1.0


Program Overview: This queries the sysjobs, sysjobschedules and sysjobhistory table to
produce a resultset showing the jobs on a server plus their schedules
(if applicable) and the maximun duration of the job.

The UNION join is to cater for jobs that have been scheduled but not yet
run, as this information is stored in the 'active_start...' fields of the
sysjobschedules table, whereas if the job has already run the schedule
information is stored in the 'next_run...' fields of the sysjobschedules table.


Modification History:
-------------------------------------------------------------------------------
Version Date Name Modification
-------------------------------------------------------------------------------
1.0 5 Jun 2002 M.Pearson Inital Creation
1.1 6 May 2009 A. Gonzalez Adapted to SQL Server 2005 and to show
subday frequencies.

*******************************************************************************/


USE msdb
Go


SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' +
substring(convert(varchar(15),active_start_date),5,2) + '/' +
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
END,
-- active_start_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+     CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = CASE len(next_run_time)
WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,
-- next_run_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+     CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]


--------------------------------
or for some more information like next run time
----------------------

select
'Server'       = left(@@ServerName,20),
'JobName'      = left(S.name,30),
'ScheduleName' = left(ss.name,25),
'Enabled'      = CASE (S.enabled)
                  WHEN 0 THEN 'No'
                  WHEN 1 THEN 'Yes'
                  ELSE '??'
                END,
'Frequency'    = CASE(ss.freq_type)
                  WHEN 1  THEN 'Once'
                  WHEN 4  THEN 'Daily'
                  WHEN 8  THEN
                    (case when (ss.freq_recurrence_factor > 1)
                        then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)
                  WHEN 16 THEN
                    (case when (ss.freq_recurrence_factor > 1)
                    then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
                  WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
                  WHEN 64 THEN 'SQL Startup'
                  WHEN 128 THEN 'SQL Idle'
                  ELSE '??'
                END,
'Interval'    = CASE
                 WHEN (freq_type = 1)                       then 'One time only'
                 WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
                 WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
                 WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
                       from (select ss.schedule_id,
                     freq_interval,
                     'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,
                     'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,
                     'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,
                     'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,
                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,
                     'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,
                     'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END
                                 from msdb..sysschedules ss
                                where freq_type = 8
                           ) as F
                       where schedule_id = sj.schedule_id
                                            )
                 WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
                 WHEN (freq_type = 32) then (select freq_rel + WDAY
                    from (select ss.schedule_id,
                                 'freq_rel' = CASE(freq_relative_interval)
                                                WHEN 1 then 'First'
                                                WHEN 2 then 'Second'
                                                WHEN 4 then 'Third'
                                                WHEN 8 then 'Fourth'
                                                WHEN 16 then 'Last'
                                                ELSE '??'
                                              END,
                                'WDAY'     = CASE (freq_interval)
                                                WHEN 1 then ' Sun'
                                                WHEN 2 then ' Mon'
                                                WHEN 3 then ' Tue'
                                                WHEN 4 then ' Wed'
                                                WHEN 5 then ' Thu'
                                                WHEN 6 then ' Fri'
                                                WHEN 7 then ' Sat'
                                                WHEN 8 then ' Day'
                                                WHEN 9 then ' Weekday'
                                                WHEN 10 then ' Weekend'
                                                ELSE '??'
                                              END
                            from msdb..sysschedules ss
                            where ss.freq_type = 32
                         ) as WS
                   where WS.schedule_id =ss.schedule_id
                   )
               END,
'Time' = CASE (freq_subday_type)
                WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
                WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
                WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
                WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
                ELSE '??'
              END,

'Next Run Time' = CASE SJ.next_run_date
                   WHEN 0 THEN cast('n/a' as char(10))
                   ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
                 END
 
   from msdb.dbo.sysjobschedules SJ
   join msdb.dbo.sysjobs         S  on S.job_id       = SJ.job_id
   join msdb.dbo.sysschedules    SS on ss.schedule_id = sj.schedule_id
order by S.name

Querying SQL Server Agent Job Information

https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

Problem

I am a BI developer and have been moved recently to a DBA role. Among the many tasks that I am responsible for, two major ones are: monitoring the SQL Server Agent Jobs running on a set of servers by creating a few standard SSRS reports and creating a Reference Manual/One Stop Guide containing the details of jobs on each of the servers. As I am new to the DBA role, to start with I would like to know some basics of what are the various SQL Server Agent Job related details which need to be reported as part of reports/documentation. Also, is there an easy/automated way to get this information instead of connecting to each server and manually getting the information through the SSMS GUI.

Solution

It is a common scenario in IT the industry where people move from a dev/test role to a DBA role and vice-versa due to various reasons. It is very essential for the person to be able to adapt easily when he/she is moved to a different role. SQL Server is known for its usability and is one of the most liked tools in the community because of its user friendliness. In this tip, we will see how to query some basic job monitoring and job setup information.  The intention of this tip is to act as a good starting point for people who are new to the DBA role and for those who need to monitor the SQL Server Agent Jobs.

Before we get into querying the required information, let's get a basic understanding of msdb database. msdb is a SQL Server System Database and is used to store the Configuration, Processing, & Metadata information related to the SQL Server features, including:

• SQL Server Agent Jobs, Job Steps, Job schedules, Alerts, Operators, etc.
• Service Broker, Log Shipping, database backups and restore information, Maintenance Plan, Database Mail, Policy Based Information of SQL Server 2008, etc.
• SQL Server Integration Services (SSIS) packages stored in Integration Services.

In this tip we will retrieve the following information related to SQL Server Agent Jobs by querying some of the system tables in the msdb database:

• Job Setup/Configuration Information
• Job Execution Information
• Job Step(s) Setup/Configuration Information
• Job Step(s) Execution Information
• Schedule Information

---

SQL Server Agent Job Setup and Configuration Information

SQL Server allows creation of jobs for performing various tasks in an automated fashion like Replication, running SSIS Packages, Stored Procedures, Batch Commands, etc. These jobs can be created either using SSMS GUI or using T-SQLqueries. Irrespective of the approach used, this information is stored in a set of SQL Server System Tables present in the msdb database.

The following query gives us the Job Level Setup and Configuration information which is also found in the SQL Server Agent Job Properties window in SSMS.

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled]
    , [sSCH].[schedule_uid] AS [JobScheduleID]
    , [sSCH].[name] AS [JobScheduleName]
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]

The following is a brief description of each of the fields returned from the above query:

• [JobID]: A unique identifier for the SQL Server Agent job (GUID).
• [JobName]: Name of the SQL Server Agent job.
• [JobOwner]: Owner of the job.
• [JobCategory]: Category to which the job belongs like Replication Snapshot, Database Maintenance, Log Shipping, etc.
• [JobDescription]: Description of the job.
• [IsEnabled]: Indicator representing whether the job is enabled or disabled.
• [JobCreatedOn]: Date and time when the job was created.
• [JobLastModifiedOn]: Date and time when the job was last modified.
• [OriginatingServerName]: Server from which the job executed.
• [JobStartStepNo]: Step number from which the job is set to start. SQL Server allows us to have multiple steps within a job and the job can be set to start from whichever step the user wants it to start from.
• [JobStartStepName]: Name of the step from which the job is set to start.
• [IsScheduled]: Indicator representing whether the job is scheduled or not. The jobs can be either scheduled to run on specified day(s) at a specified time or can be invoked through code like T-SQL, etc.
• [JobScheduleID]: Unique identifier of the schedule associated with the job (GUID).
• [JobScheduleName]: Name of the schedule associated with the job. SQL Server allows us to associate multiple schedules with one job, in which case, the above query would return one row for each schedule associated with each job.
• [JobDeletionCriterion]: The criterion for deleting the job. SQL Server Agent has a feature which allows us to delete/drop the job based on a certain criterion so that there is no need to manually delete/cleanup the jobs.

---

SQL Server Agent Job Execution Information

SQL Server Agent stores the history of job execution in system tables in msdb database.

The following query gives us the details of last/latest execution of the SQL Server Agent Job and also the next time when the job is going to run (if it is scheduled). This information can also be found in the Job History/Job Activity Monitor windows in SSMS.

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , CASE 
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') 
        AS [LastRunDuration (HH:MM:SS)]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [NextRunDateTime]
FROM 
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT 
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id] 
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]

The following is a brief description of each of the fields returned from the above query:

• [JobID]: A unique identifier for the SQL Server Agent job (GUID) for which the execution history is being reported.
• [JobName]: Name of the SQL Server Agent job.
• [LastRunDateTime]: Date and time when the job was run last time (corresponds to the most recent run).
• [LastRunStatus]: Status or outcome of the last job run.
• [LastRunDuration (HH:MM:SS)]: Duration of the last run represented in Hours:Minutes:Seconds format.
• [LastRunStatusMessage]: More descriptive message about the job status/outcome.
• [NextRunDateTime]: Date and time when the job will run next time. This information is available only for the jobs which are scheduled (a schedule is associated with a job).

---

SQL Server Agent Job Steps Setup and Configuration Information

In SQL Server Agent, a job is the parent level entity, which contains one or more steps. A step is the child/low level entity, which contains the actual commands/instructions for performing a specific task. Whereas a job defines the sequence of execution of steps, schedule for running steps, etc.

The following query gives us the Job Step level Setup and Configuration information, which can also be found in the Job Step Properties window in SSMS.

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' 
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) 
                    + ' ' 
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' 
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) 
                    + ' ' 
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
ORDER BY [JobName], [StepNo]

The following is a brief description of each of the fields returned from the above query:

• [JobID]: A unique identifier for the SQL Server Agent job (GUID) to which the step(s) belongs.
• [JobName]: Name of the SQL Server Agent job.
• [StepID]: A unique identifier for the SQL Server Agent Job Step (GUID).
• [StepNo]: Sequence number of the step representing the position of the step in the job.
• [StepName]: Name of the SQL Server Agent Job Step.
• [StepType]: Subsystem/Type of the Job Step like SQL Server Integration Services Package, Transact-SQL Script (T-SQL), ActiveX Script etc.
• [RunAs]: Account under which the job step should be run/executed. This will contain a value in the above query output only when run through a proxy.
• [Database]: Name of the database in which the command is executed. This applies only when the Step Type is Transact-SQL Script (T-SQL).
• [ExecutableCommand]: The actual command which will be executed by the subsystem.
• [OnSuccessAction]: Action to be taken by SQL Server Agent when the job step succeeds.
• [RetryAttempts]: Number of retry attempts made by SQL Server Agent in case the job step fails.
• [RetryInterval (Minutes)]: Time interval in minutes between each retry attempt in case the job step fails and SQL Server Agent tries to re-run it.
• [OnFailureAction]: Action to be taken by SQL Server Agent when the job step fails.

---

SQL Server Agent Job Steps Execution Information

SQL Server Agent stores the history of the execution of each of the job steps in system tables in msdb database.

The following query gives us the details of last/latest execution of the job step. This information can also be found in the Job History/Log File Viewer windows in SSMS.

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[last_run_outcome]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 5 THEN 'Unknown'
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':')
      AS [LastRunDuration (HH:MM:SS)]
    , [sJSTP].[last_run_retries] AS [LastRunRetryAttempts]
    , CASE [sJSTP].[last_run_date]
        WHEN 0 THEN NULL
        ELSE 
            CAST(
                CAST([sJSTP].[last_run_date] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
ORDER BY [JobName], [StepNo]

The following is a brief description of each of the fields returned from the above query:

• [JobID]: A unique identifier for the SQL Server Agent job (GUID) to which the step(s) belongs.
• [JobName]: Name of the SQL Server Agent job.
• [StepID]: A unique identifier for the SQL Server Agent Job Step (GUID).
• [StepNo]: Sequence number of the step representing the position of the step in the job.
• [StepName]: Name of the SQL Server Agent Job Step.
• [LastRunStatus]: Status or Outcome of the step execution when the job/step executed last time.
• [LastRunDuration (HH:MM:SS)]: Duration of the last run represented in Hours:Minutes:Seconds format.
• [LastRunRetryAttempts]: Number of retry attempts made by SQL Server Agent during the last run of the job step.
• [LastRunDateTime]: Date and time when the job step was last run.

---

SQL Server Agent Job Schedule Information

SQL Server allows creating schedules for performing various tasks at a specified date and time. This can be a one time schedule or a recurring schedule with or without an end date. Each schedule can be associated with one or more SQL Server Agent Jobs.

The following query gives us the list of schedules created/available in SQL Server and the details (Occurrence, Recurrence, Frequency, etc.) of each of the schedules.

SELECT 
    [schedule_uid] AS [ScheduleID]
    , [name] AS [ScheduleName]
    , CASE [enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , CASE 
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' ' 
                     + CASE [freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' 
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]
    , STUFF(
            STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageStartDate]
    , STUFF(
            STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageEndDate]
    , [date_created] AS [ScheduleCreatedOn]
    , [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]

The following is a brief description of each of the fields returned from the above query:

• [ScheduleID]: Unique identifier of the schedule (GUID).
• [ScheduleName]: Name of the schedule. SQL Server allows one schedule to be associated with more than one job.
• [IsEnabled]: Indicator representing whether a schedule is enabled or disabled.
• [ScheduleType]: The type of the schedule.
• [Occurrence]: Occurrence of the schedule like Daily, Weekly, Monthly, etc.
• [Recurrence]: Recurrence of the schedule like specific day(s), Specific Days of the Week, Number of weeks, etc.
• [Frequency]: How frequently the job should run on the day(s) when it is scheduled to run such as: Occurs only once on the scheduled day(s), Occurs every 2 hours on the scheduled day(s) etc. between specified start and end times.
• [ScheduleUsageStartDate]: Effective start date from when the schedule will be used.
• [ScheduleUsageEndDate]: Effective end date after which the schedule will not be used.
• [ScheduleCreatedOn]: Date and time when the schedule was created.
• [ScheduleLastModifiedOn]: Date and time when the schedule was last modified.

GET Current Timezone in SQL Server

http://blog.sqlauthority.com/2014/02/15/sql-server-get-current-timezone-name-in-sql-server/

DECLARE @TimeZone VARCHAR(50)EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUTSELECT @TimeZone