Posted By

hairajeshk on 07/21/14

Tagged

search
text

Versions (?)

Last Edited at 07/21/14 05:24pm

Statistics

Viewed 1125 times

Favorited by 0 user(s)

Related snippets

Search for text in all db objects in database

/ Published in: SQL

from codeproject

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

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))