Advanced Search

Return to Snippet

Revision: 66916
at July 21, 2014 17:24 by hairajeshk
Initial Code 
DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'foobar', @notcontain = ''

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0 
or CHARINDEX(@notcontain,syscomments.text)<>0))
Initial URL 
http://www.codeproject.com/Tips/603593/Find-text-in-Stored-Procedures-View-Trigger-and-Fu
Initial Description 
from codeproject
Initial Title 
Search for text in all db objects in database
Initial Tags 
search, text
Initial Language 
SQL