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] 
@cSearch1VarChar(255)
,@cSearch2VarChar(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 WhoEmail
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16/11/00Julian Haynesme@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