Technical Article

Find a string in DB objects.

,

Find a string in Procedures, Triggers, Constraints, Defaults, Functions, and Views. 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.

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

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating