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, views 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 = ‘ProductCode’

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

Advertisements

Got something to add?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s