Search For SPs which contain a specific String.

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  October 1, 2007 at 2:22 am

  #181763

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

  Prasad Bhogadi
  www.inforaise.com

• rwaithaka

  SSC Rookie

  Points: 41

  More actions

  February 5, 2010 at 3:09 am

  #1114273

  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.