Search For SPs which contain a specific String.

/* TITLE : PROCEDURE WHICH RETURNS LIST OF ALL THE USER DEFINED STORED PROCEDURES WHICH CONTAIN A SPECIFIC STRING.
AUTHOR : PRASAD BHOGADI
ORGANIZATION : INFORAISE TECHNOLOGIES PVT LTD, INDIA.
*/

ALTER PROCEDURE SearchForStringInSPs @searchfor VARCHAR(100)
AS
DECLARE @spcode varchar(8000),
@spname varchar(100),
@occurance int,
@rowcount int
set nocount on
CREATE TABLE #SPNAMES 
(SPNAME varchar(100))

DECLARE GETSPNAME CURSOR FOR 
SELECT  NAME 
FROM 
sysobjects 
WHERE 
type='P' 
AND 
category=0

OPEN GETSPNAME 

FETCH NEXT FROM GETSPNAME INTO @spname

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE GETSPCODE CURSOR FOR

SELECT syscomments.text

FROM 
sysobjects,syscomments

WHERE 
sysobjects.id = syscomments.id

AND 
sysobjects.type = 'P'

AND 
sysobjects.name = @spname

OPEN GETSPCODE 
FETCH  NEXT FROM GETSPCODE into @spcode
WHILE @@FETCH_STATUS =0
BEGIN
SET @occurance = (SELECT CHARINDEX(@searchfor,@spcode))
IF @occurance > 0
BEGIN
INSERT INTO #SPNAMES(SPNAME ) VALUES(@spname)
END
FETCH  NEXT FROM GETSPCODE into @spcode
SET @rowcount=@rowcount-1
END
CLOSE GETSPCODE
DEALLOCATE GETSPCODE

FETCH NEXT FROM GETSPNAME INTO @spname
END
CLOSE GETSPNAME
DEALLOCATE GETSPNAME 
SELECT DISTINCT(LTRIM(RTRIM(SPNAME))) FROM #SPNAMES

---------------------------------------------------------------------
--Modified
---------------------------------------------------------------------
/* TITLE : PROCEDURE WHICH RETURNS LIST OF ALL THE USER DEFINED STORED PROCEDURES WHICH CONTAIN A SPECIFIC STRING.
AUTHOR : PRASAD BHOGADI
ORGANIZATION : INFORAISE TECHNOLOGIES PVT LTD, INDIA.
*/

create PROCEDURE SearchForStringInSPsUpd @searchfor VARCHAR(100)
AS
DECLARE @spcode varchar(8000),
@spname varchar(100),
@occurance int,
@rowcount int
set nocount on
CREATE TABLE #SPNAMES 
(SPNAME varchar(100))


DECLARE GETSPCODE CURSOR FOR

SELECT syscomments.text,
sysobjects.name

FROM 
sysobjects,syscomments

WHERE 
sysobjects.id = syscomments.id

AND 
sysobjects.type = 'P'
AND 
sysobjects.category=0

OPEN GETSPCODE 
FETCH  NEXT FROM GETSPCODE into @spcode,@spname
WHILE @@FETCH_STATUS =0
BEGIN
SET @occurance = (SELECT CHARINDEX(@searchfor,@spcode))
IF @occurance > 0
BEGIN
INSERT INTO #SPNAMES(SPNAME ) VALUES(@spname)
END
FETCH  NEXT FROM GETSPCODE into @spcode,@spname
SET @rowcount=@rowcount-1
END
CLOSE GETSPCODE
DEALLOCATE GETSPCODE


SELECT DISTINCT(LTRIM(RTRIM(SPNAME))) FROM #SPNAMES