Proc to Query Syscomments and Sysobjects

,

TSQL:Procedure to Query for Dependent Objects

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 dependednt objects or references.

I also created another script to query on msdb..sysjobs and msdb..sysjobsteps for phrase or objects.

Here is the script and make sure you don't grant access to public. I used this as 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 syscomments.

It is just a starting place to look for outdated or dependent code references.

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 
-- AS IS 
--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

Share

Share

Rate