Advanced Search

Return to Snippet

Revision: 21147
at December 4, 2009 13:15 by kencyber
Initial Code 
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'
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
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.

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Search stored procedures for the occurance of a string

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL