Posted By

kencyber on 12/04/09


Tagged

stored ProceduresCode SearchProcedures


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

kencyber
Tyster


Search stored procedures for the occurance of a string


 / Published in: SQL
 

Finds all procedures that contain any occurrence of a string. The results show the name of the procedure, the character index where the first match was found along with the portion of the code that matched the search term.

  1. declare @term varchar(2000)
  2. SELECT @term = REPLACE('CROSS JOIN', '%', '\%')
  3.  
  4. SELECT ROUTINE_NAME, PATINDEX('%' + @term + '%', ROUTINE_DEFINITION) AS CharacterIndex,
  5. SUBSTRING(ROUTINE_DEFINITION, PATINDEX('%' + @term + '%', ROUTINE_DEFINITION) - 20, 40 + LEN(@term)) AS MatchingCode
  6. FROM INFORMATION_SCHEMA.ROUTINES
  7. WHERE ROUTINE_DEFINITION LIKE '%' + @term + '%'
  8. AND ROUTINE_TYPE='PROCEDURE'

Report this snippet  

You need to login to post a comment.