Posted By

kencyber on 12/04/09


Tagged

search FunctionsKeyword


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


Search functions for the occurance of a string


 / Published in: SQL
 

Searches the code for all functions in the current database and returns results containing the function name, the character index where the first match was found, and a portion of the code where the match occurred.

  1. declare @term varchar(2000)
  2. SELECT @term = REPLACE('CROSS JOIN', '%', '\%')
  3.  
  4. SELECT ROUTINE_NAME, ROUTINE_TYPE, 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='FUNCTION'

Report this snippet  

You need to login to post a comment.