Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Search For SPs which contain a specific String. Expand / Collapse
Author
Message
Posted Monday, October 1, 2007 2:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:47 AM
Points: 967, Visits: 450
Comments posted to this topic are about the item Search For SPs which contain a specific String.

Prasad Bhogadi
www.inforaise.com
Post #404891
Posted Friday, February 5, 2010 3:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 7, 2013 8:54 AM
Points: 27, Visits: 102
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.



Post #860275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse