Click here to Skip to main content
15,029,296 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick
Posted 6 Jun 2013

Stats

107.6K views
7 bookmarked

Find text in Stored Procedures, View, Trigger, and Function

Rate me:
Please Sign up or sign in to vote.
4.70/5 (8 votes)
6 Jun 2013CPOL
Find text in Stored Procedures, View, Trigger, and Function.

Introduction

I have often needed to find a stored procedure, view, trigger, and function that contains a certain piece of text, such as a text in a subject line.  

Typically, people are trying to find all the stored procedures that reference a specific object. The best place to do this kind of searching is through your source control tool (you do keep your database objects in source control, don't you?), there are certainly some ways to do this in the database. 

Using the code

Let's say you are searching for 'foobar' in all your stored procedures, view, trigger, and function. You can do this using syscomments:  

SQL
DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'foobar', @notcontain = ''

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]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0 
or CHARINDEX(@notcontain,syscomments.text)<>0))  

If you want to remove some string from searching you can always change the variable value "@notcontain" to the corresponding value, example (remove word "comment" in the search above): 

SQL
SELECT @SEARCHSTRING = 'foobar', @notcontain = 'comment'

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nelson Kosta Souto
Engineer
Portugal Portugal
My name is Nelson Souto, I develop software for fun and profit since 1992.

Comments and Discussions

 
QuestionBetter solution Pin
Member 920943828-Sep-17 3:14
MemberMember 920943828-Sep-17 3:14 
QuestionI try this code Pin
Santanu24B17-Aug-17 2:34
professionalSantanu24B17-Aug-17 2:34 
DECLARE @MyText varchar(255)
SET @MyText ='ABCD'

SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+ @MyText +'%'
ORDER BY 2,1
GeneralMy vote of 5 Pin
Hugo Durana6-Jun-13 11:11
MemberHugo Durana6-Jun-13 11:11 
QuestionAlternative Pin
Wendelius6-Jun-13 3:28
mveWendelius6-Jun-13 3:28 
AnswerRe: Alternative Pin
Davinder Singh21-Aug-13 4:44
MemberDavinder Singh21-Aug-13 4:44 
GeneralRe: Alternative Pin
Nelson Kosta Souto2-Sep-13 0:25
professionalNelson Kosta Souto2-Sep-13 0:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.