Tuesday, April 30, 2013

How to: search for a word or column name across the Procedure, Function, Trigger, View

 

DECLARE @SearchPhrase nvarchar(1000)
 
SET @SearchPhrase = N'backup'   -- Place your search string
 
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],
(SELECT ParentTable.[Name]
FROM sysobjects ParentTable
WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable
FROM sysobjects,
     syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR', 'V')
--AND sysobjects.category = 0  --- To restrict only user defined object
AND CHARINDEX(@SearchPhrase, syscomments.text) > 0

1 comment:

magnifictraining said...

Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!!
Hadoop online training