Find a string in Procedures, Triggers, Constraints, Defaults, Functions, and Views

				
--**************************************
-- Name: Find a string in Procedures, Triggers, Constraints, Defaults, Functions,and Views
-- Description:Allow users to search thru objects for a value they are looking. This may be items that touch a specific table/view or items that need to be replaced.
-- By: James Travis
--
-- Inputs:@find = the value(s) to search for
@type = the type of objects to search
--
-- Returns:The names of objects that containt th search criteria.
--
-- Assumes:First off this will not work for any items that have the WITH ENCRYPTION remark in them. With this is can pose a string such as 'INSERT' against all the 'P'rocedures to get a return of which Procedures have an INSERT statment in them or you can do word strings such as 'FROM TABLE1 WHERE COL1 =' or string list searches such as 'INSERT%TABLE1%COL1 ='. You can use any valid like strings you wish, but you don't need leading and ending wildcards as general most items start with a specific item such as CREATE which will be contained in the majority of code items. This is compatible with SQL 7/2000.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=414&lngWId=5--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