Technical Article

Search for a string in a procedure, trigger or fun

,

This is an extension of two previous scripts, this one will produce a list of ojects that contain the first parameter and not the second. The function works as a table so if the user wants to list only functions, query as: select * from fn_sys_searchobject('text1','text2') where object_type = 'function'

CREATE function fn_sys_searchobject 
(@SEARCHSTRING VARCHAR(255), @notcontain Varchar(255))

RETURNS @out_table TABLE (
Objects_name varchar(255),
Type varchar(25))
AS
BEGIN
Insert @out_Table
SELECT DISTINCT sysobjects.name ,  
 case when sysobjects.xtype = 'P' then 'procedure'
 when sysobjects.xtype = 'TF' then 'Function'
 when sysobjects.xtype = 'TR' then 'Trigger'
 end as Object_type
 FROM sysobjects,syscomments
 WHERE sysobjects.id =     syscomments.id
 AND sysobjects.type in ('P','TF','TR')
 AND sysobjects.category = 0
 AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
 AND CHARINDEX(@notcontain,syscomments.text)=0

Return
end

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating