Text Search of Stored Procedures 2

[aruba]

Is there any way that I can do a text search through the contents of all Stored Procedures? I'm trying to find all Stored Procedures that run only on a certain day of the week.
Thanks.

 

[Katy44]

The only way I can do that is to script the database (stored procedures only) and then search through the resulting SQL file.

 

[dbomrrsm]

This SP - I believe - will do what you want - hope it helps.

USE master
go
DROP PROCEDURE sp_FindObject
GO
CREATE PROCEDURE sp_FindObject
@SearchString varchar (255)
AS
SET nocount ON
DECLARE @Name varchar(255)
DECLARE @Text nvarchar(4000)
CREATE TABLE #Objs
( ObjName varchar (255))
DECLARE Obj CURSOR
FOR SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc WHERE (so.type = "P" or so.type = "V") AND so.id = sc.id
OPEN Obj
FETCH Next FROM Obj INTO @Name,@Text
WHILE @@FETCH_STATUS=0


BEGIN
IF PATINDEX(@SearchString,@Text) <> 0
INSERT INTO #Objs VALUES (@Name)
FETCH Next FROM Obj INTO @Name,@Text
END
CLOSE Obj
DEALLOCATE Obj
SELECT objname FROM #Objs GROUP BY objname
DROP TABLE #Objs
go

DBomrrsm

 

[Katy44]

Thank you.
A much neater way!

 

[aruba]

Thanks so much! That's what I was looking for.

 

[ESquared]

I hope you don't mind, but... I'm wondering why use a cursor when you don't have to:

DECLARE
	@SearchString varchar(400)
SET @SearchString = 'whatever'

SELECT
		[name], [text]
	FROM SysObjects O INNER JOIN SysComments C on O.id = C.id
	WHERE
		(type = 'P' OR type = 'V')
		AND [text] Like '%' + @SearchString + '%'

Note that this can miss finding text that just happens to be split on the 4000-character boundary, because the text of objects is chopped up into pieces of this size.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[dbomrrsm]

ESquared

Used a cursor because didnt have the brains to do it like you suggest above - very nice tip which I am sure will be of great use in the future.

Thanks

DBomrrsm

PS - Wont take such a long time to write either. Thanks again

 

[hmckillop]

I use something similiar and have posted it before, just another variation on the same theme.

CREATE PROC sp_search_code    
(    
@SearchStr  varchar(100),    
@RowsReturned int = NULL OUT    
)    
AS    
    
/*    
To search your database code for the keyword 'unauthorized':    
EXEC sp_search_code 'unauthorized'    
    
To search your database code for the keyword 'FlowerOrders' and also find out the number of hits:    
DECLARE @Hits int    
EXEC sp_search_code 'FlowerOrders', @Hits OUT    
SELECT 'Found ' + LTRIM(STR(@Hits)) + ' object(s) containing this keyword' AS Result    
*************************************************************************************************/    
BEGIN    
 SET NOCOUNT ON    
    
 SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',    
  CASE     
    WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1     
    THEN 'Replication stored procedure'    
    WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1     
    THEN 'Extended stored procedure'        
   WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1     
    THEN 'Stored Procedure'     
   WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1     
    THEN 'Trigger'     
   WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1     
    THEN 'Table-valued function'     
   WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1     
    THEN 'Scalar-valued function'    
    WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1     
    THEN 'Inline function'     
  END AS 'Object type',    
  'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'    
 FROM syscomments c    
  INNER JOIN    
  sysobjects o    
  ON c.id = o.id    
 WHERE c.text LIKE '%' + @SearchStr + '%' AND    
  encrypted = 0    AND    
  (    
  OBJECTPROPERTY(c.id, 'IsReplProc') = 1  OR    
  OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR    
  OBJECTPROPERTY(c.id, 'IsProcedure') = 1  OR    
  OBJECTPROPERTY(c.id, 'IsTrigger') = 1  OR    
  OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR    
  OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR    
  OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1     
  )    
    
 ORDER BY 'Object type', 'Object name'    
    
 SET @RowsReturned = @@ROWCOUNT    
END

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.