Search For SPs which contain a specific String.

  • Comments posted to this topic are about the item Search For SPs which contain a specific String.

    Prasad Bhogadi
    www.inforaise.com

  • if OBJECT_ID ('SearchForStringInSPsUpd','P') is not null

    drop proc SearchForStringInSPsUpd

    Go

    create PROCEDURE SearchForStringInSPsUpd @searchfor VARCHAR(100)

    AS

    DECLARE @spcode varchar(8000),

    @spname varchar(100),

    @occurance int,

    @rowcount int,

    @xtype varchar(20)

    set nocount on

    CREATE TABLE #SPNAMES

    (SPNAME varchar(100),SPTYPE varchar(20))

    DECLARE GETSPCODE CURSOR FOR

    SELECT syscomments.text,sysobjects.name,sysobjects.type

    FROM

    sysobjects,syscomments

    WHERE sysobjects.id = syscomments.id AND (sysobjects.type = 'P' )AND sysobjects.category=0

    union all

    SELECT syscomments.text,sysobjects.name,sysobjects.type

    FROM

    sysobjects,syscomments

    WHERE sysobjects.id = syscomments.id AND ( sysobjects.type = 'FN') AND sysobjects.category=0

    union all

    SELECT syscomments.text,sysobjects.name,sysobjects.type

    FROM

    sysobjects,syscomments

    WHERE sysobjects.id = syscomments.id AND ( sysobjects.type = 'TR')AND sysobjects.category=0

    OPEN GETSPCODE

    FETCH NEXT FROM GETSPCODE into @spcode,@spname,@xtype

    WHILE @@FETCH_STATUS =0

    BEGIN

    SET @occurance = (SELECT CHARINDEX(@searchfor,@spcode))

    IF @occurance > 0

    BEGIN

    INSERT INTO #SPNAMES(SPNAME,SPTYPE ) VALUES(@spname,@xtype)

    END

    FETCH NEXT FROM GETSPCODE into @spcode,@spname,@xtype

    SET @rowcount=@rowcount-1

    END

    CLOSE GETSPCODE

    DEALLOCATE GETSPCODE

    SELECT DISTINCT (LTRIM(RTRIM(SPNAME))) as ObjectName,SPTYPE as ObjectType FROM #SPNAMES

    Go

    -- I have tweaked the code a bit to search the string in triggers and functions. Enjoy thanks PRASAD.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply