Procedure Search by Keyword(s)

,

Searches SysComments for occurances of entered keywords and returns a list of procedures that contain the keywords.

eg1) To find all procedures with 'nMyColumn' in

EXEC utl_ProcSearch 'nMyColumn'

eg2) To find all procedures with 'nMyColumn' in and 'nMyColumn2' in

EXEC utl_ProcSearch 'nMyColumn', 'nMyColumn2'

eg3) To find all procedures with temporary tables in...

EXEC utl_ProcSearch '#'

NB: Triggers also returned

Caviate: SysComments splits procedures into rows of 4000 chrs each so there is a very small chance that it could miss a procedure if the keyword spans 2 rows. As the chances of this are minimal the benfits out weigh this

CREATE PROCEDURE [dbo].[utl_ProcSearch] 
	@cSearch1	VarChar(255)
	,@cSearch2	VarChar(255) = ''
AS
/********************************************************************************************************************************************************
Purpose: 	Handy little proc to search all sps for a piece of text
		Pass in two pieces of text to get sps that contain both

Amendment Log
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date 		Who			Email
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16/11/00	Julian Haynes		me@julianhaynes.freeserve.co.uk
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/
SET NOCOUNT ON

IF @cSearch2 = ''

	SELECT DISTINCT
		LEFT(P.Name, 30) 'Procedure'
	FROM
		SysComments C
		JOIN SysObjects P
			ON P.ID = C.ID 
			AND (P.Type = 'P' OR P.Type = 'TR')
			AND Left(P.Name,3) <> 'dt_'
	WHERE
		C.Text LIKE '%' + @cSearch1 +'%'
	ORDER BY
		1
ELSE

	SELECT DISTINCT
		LEFT(P.Name, 30) 'Procedure'
	FROM
		SysComments C
		JOIN SysObjects P
			ON P.ID = C.ID 
			AND (P.Type = 'P' OR P.Type = 'TR')
			AND Left(P.Name,3) <> 'dt_'
	WHERE
		C.Text LIKE '%' + @cSearch1 +'%' AND C.Text LIKE '%' + @cSearch2 +'%'
	ORDER BY
		1

Rate

Share

Share

Rate