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.