Search stored procedures for the occurance of a string


/ Published in: SQL
Save to your folder(s)

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.


Copy this code and paste it in your HTML
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.