Posted By

hairajeshk on 07/21/14


Tagged

search stored text functions in for views Procs


Versions (?)

Search for text in all db objects in database


 / Published in: SQL
 

URL: http://www.codeproject.com/Tips/603593/Find-text-in-Stored-Procedures-View-Trigger-and-Fu

from codeproject

  1. DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)
  2.  
  3. SELECT @SEARCHSTRING = 'foobar', @notcontain = ''
  4.  
  5. SELECT DISTINCT sysobjects.name AS [Object Name] ,
  6. case when sysobjects.xtype = 'P' then 'Stored Proc'
  7. when sysobjects.xtype = 'TF' then 'Function'
  8. when sysobjects.xtype = 'TR' then 'Trigger'
  9. when sysobjects.xtype = 'V' then 'View'
  10. end AS [Object Type]
  11. FROM sysobjects,syscomments
  12. WHERE sysobjects.id = syscomments.id
  13. AND sysobjects.type IN ('P','TF','TR','V')
  14. AND sysobjects.category = 0
  15. AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
  16. AND ((CHARINDEX(@notcontain,syscomments.text)=0
  17. OR CHARINDEX(@notcontain,syscomments.text)<>0))

Report this snippet  

You need to login to post a comment.