Posted By

kencyber on 12/04/09

Tagged

Versions (?)

Last Edited at 12/04/09 01:15pm

Statistics

Viewed 1674 times

Favorited by 2 user(s)

Related snippets

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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

DECLARE @term VARCHAR(2000)
SELECT @term = REPLACE('CROSS JOIN', '%', '\%')
 
SELECT ROUTINE_NAME, PATINDEX('%' + @term + '%', ROUTINE_DEFINITION) AS CharacterIndex,
	SUBSTRING(ROUTINE_DEFINITION, PATINDEX('%' + @term + '%', ROUTINE_DEFINITION) - 20, 40 + LEN(@term)) AS MatchingCode
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + @term + '%'
	AND ROUTINE_TYPE='PROCEDURE'

Report this snippet Tweet

Comments

You need to login to view comments.