--**************************************
--     
-- Name: Find a string in Procedures, Tr
--     iggers, Constraints, Defaults, Functions
--     ,and Views
-- Description:Allow users to search thr
--     u objects for a value they are looking. 
--     This may be items that touch a specific 
--     table/view or items that need to be repl
--     aced.
-- By: James Travis
--
-- Inputs:@find = the value(s) to search
--      for
@type = the type OF objects TO search
--
-- Returns:The names of objects that con
--     taint th search criteria.
--
-- Assumes:First off this will not work 
--     for any items that have the WITH ENCRYPT
--     ION remark in them. With this is can pos
--     e a string such as 'INSERT' against all 
--     the 'P'rocedures to get a return of whic
--     h Procedures have an INSERT statment in 
--     them or you can do word strings such as 
--     'FROM TABLE1 WHERE COL1 =' or string lis
--     t searches such as 'INSERT%TABLE1%COL1 =
--     '. You can use any valid like strings yo
--     u wish, but you don't need leading and e
--     nding wildcards as general most items st
--     art with a specific item such as CREATE 
--     which will be contained in the majority 
--     of code items. This is compatible with S
--     QL 7/2000.
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/xq/ASP/txtCod
--     eId.414/lngWId.5/qx/vb/scripts/ShowCode.
--     htm--for details.--**************************************
--     

CREATE PROCEDURE sp_FindStringInCode
/* Input variables, DEFAULT NULL FOR custom error output. */
@find VARCHAR(50) = NULL,
@type VARCHAR(2) = NULL
AS
/* CHECK FOR NULL or invalid input AND show custom error. */
IF @find IS NULL AND @type IS NULL
    BEGIN
    	RAISERROR ('This PROCEDURE has two required parameters @find AND @type',16,-1)
    	RETURN
END

ELSE IF @find IS NULL
    BEGIN
    	RAISERROR ('You must enter a valid LIKE criteria FOR @find without the leading/ending % wildcard.',16,-1)
    	RETURN
END

ELSE IF @type IS NULL OR @type NOT IN ('C','D','FN','P','TR','V')
    BEGIN
    	RAISERROR('No value was entered FOR @type.
    Valid VALUES FOR @type are
    	C = CHECK CONSTRAINT
    	D = DEFAULT
    	FN = Function
    	P = PROCEDURE
    	TR = TRIGGER
    	V = View',16,-1)
    	RETURN
END

/* SET wildcards ON END OF find value. */
SET @find = '%' + @find + '%'
/* Output object names which contain find value. */
SELECT DISTINCT OBJECT_NAME([id]) FROM syscomments
WHERE [id] IN (SELECT [id] FROM sysobjects WHERE xtype = @type AND status >= 0) AND [text] LIKE @find