Chris Barba

Sql Server Keyword search across Stored Procedures, Functions, Triggers

Here is some T-SQL I got from a friend of mine (David Mccoullgh). Just set the @SearchPhrase to search across stored proc, functions, triggers to see where that string is in use. Very helpful if you are changing a field name and want to know where it is in use.

DECLARE @SearchPhrase varchar(1000)

SET @SearchPhrase = 'PlayerBonus'

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'
                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')
            AND sysobjects.category = 0
            AND CHARINDEX(@SearchPhrase, syscomments.text) > 0

posted @ Monday, December 07, 2009 7:53 AM