Find a string in DB objects.

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