Search For SPs which contain a specific String.

,

This Stored Procedure is for Searching all the user defined Stored Procedures in a database for the existence of a specified string . This is especially useful when you have changed some Column name and want to Find the Column Name in all the Stored Procedures where it is used, so it will return you all the SPs which contain that Column Name in them.

Well I realized that I used an additional Cursor for looping through for the SP Name which can be accomplished in the cursor which is used for retrieving the SP Text itself so I modified the Stored procedure for little optimization.

/* 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

Rate

Share

Share

Rate