Please visit our sponsor
UNKNOWN --************************************** -- 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. -- --Side Effects:None --This code is copyrighted and has limited warranties. --Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.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