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