Search for text in all db objects in database


/ Published in: SQL

from codeproject


Copy this code and paste it in your HTML
  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))

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.