Search For SPs which contain a specific String.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Prasad Bhogadi » Search For SPs which contain a specific...

Search For SPs which contain a specific String.

Rate Topic

Display Mode

Topic Options

Author

Message

Prasad Bhogadi

Posted Monday, October 01, 2007 2:22 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 02, 2013 5:00 AM
Points: 967, Visits: 447

Comments posted to this topic are about the item Search For SPs which contain a specific String.

Prasad Bhogadi

Post #404891

rwaithaka

Posted Friday, February 05, 2010 3:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 2:19 AM
Points: 27, Visits: 101

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 »

Permissions