Technical Article

Proc to Query MSDB..SYSJOBS and SYSJOBSTEPS

,

TSQL:Procedure to Lookup Jobs

Jack Donnell,
jack@JackDonnell.com

Have you ever kept altering the same query to look for different items in you database?
I took a hint from MS with all the sp_help and other system stored procedures and made
a two scripts this one to query for databases scheduled tasks or jobs as they are now called.

The other one performs a similar query on syscomments for phrase or objects.

Here is the script and make sure you don't grant access to public. this was a quick solution
and there are tons of ways to probably improve it. I just need some thing to get me started to
in my search to help Identify certain dependent objects or attributes within jobs.
It does not go into DTS packages and search for code, but a good starting place.

SET QUOTED_IDENTIFIER OFF 

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME = 'LOOK_UP_JOBS_NONPROD')
DROP PROCEDURE LOOK_UP_JOBS_NONPROD 
GO 

CREATE PROCEDURE LOOK_UP_JOBS_NONPROD 
--EX. Exec LOOK_UP_JOBS_NONPROD 'STEP NAME
--EX. Exec LOOK_UP_JOBS_NONPROD 'USER_TABLE_1
--EX. Exec LOOK_UP_JOBS_NONPROD 'SP_
--EX. Exec LOOK_UP_JOBS_NONPROD 'select id from idtable' 
--EX. Exec LOOK_UP_JOBS_NONPROD 'SP_GET_DATA_LOOKUP'
--
--Local Variable 
--
@SEARCH_STRING VARCHAR(255) 
AS 
--
--Clean up Search String
--
SELECT @SEARCH_STRING = RTRIM(@SEARCH_STRING) 
SELECT @SEARCH_STRING = LTRIM(@SEARCH_STRING) 
SELECT @SEARCH_STRING = REPLACE(@SEARCH_STRING,' ','%') 
SELECT @SEARCH_STRING = REPLACE(@SEARCH_STRING,'"','%') 
SELECT @SEARCH_STRING = REPLACE(@SEARCH_STRING,"'",'%') 
SELECT @SEARCH_STRING = REPLACE(@SEARCH_STRING,'','%') 
SELECT @SEARCH_STRING = '%'+ @SEARCH_STRING +'%' 
--------------------------------------------------------------- 
SELECT @SEARCH_STRING AS 'SEARCH_STRING_USED' 
--------------------------------------------------------------- 

SELECT  CONVERT(VARCHAR(10),@@SERVERNAME) AS SEVER_NAME, 
        CONVERT(VARCHAR(42),J.NAME) AS 'JOBNAME', 
        JS.STEP_ID, 
        CONVERT(VARCHAR(50),JS.STEP_NAME) AS 'STEP_NAME', 
        JS.LAST_RUN_DATE, 
        JS.LAST_RUN_TIME, 
        CONVERT(VARCHAR(35),JS.DATABASE_NAME)AS DATABASE_NAME , 
        CASE J.ENABLED WHEN 1 THEN 'YES' ELSE 'NO' END AS 'ENABLED' 
        FROM    MSDB..SYSJOBS AS J JOIN 
                MSDB..SYSJOBSTEPS AS JS ON J.JOB_ID=JS.JOB_ID 
        WHERE   J.NAME NOT LIKE 'Backup %' 
                AND (J.NAME LIKE @SEARCH_STRING         
                        OR JS.COMMAND LIKE @SEARCH_STRING 
                        OR J.NAME LIKE @SEARCH_STRING 
                        OR JS.STEP_NAME LIKE @SEARCH_STRING 
                        OR JS.DATABASE_USER_NAME LIKE @SEARCH_STRING 
                        OR JS.DATABASE_NAME LIKE @SEARCH_STRING 
                        OR JS.OUTPUT_FILE_NAME LIKE @SEARCH_STRING 
                        OR JS.LAST_RUN_DATE LIKE @SEARCH_STRING 
                        OR JS.LAST_RUN_TIME LIKE @SEARCH_STRING 
                        OR JS.SERVER LIKE @SEARCH_STRING) 
        
        GROUP BY J.NAME,JS.STEP_ID,JS.STEP_NAME,J.ENABLED,JS.LAST_RUN_DATE, 
                        JS.LAST_RUN_TIME,JS.DATABASE_NAME,JS.DATABASE_USER_NAME 
        
        ORDER BY J.NAME,JS.STEP_ID,JS.STEP_NAME,JS.LAST_RUN_DATE,JS.LAST_RUN_TIME,JS.DATABASE_NAME 
GO

Rate

4 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating