October 1, 2007 at 2:22 am
Comments posted to this topic are about the item Search For SPs which contain a specific String.
Prasad Bhogadi
www.inforaise.com
February 5, 2010 at 3:09 am
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.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy