Click here to Skip to main content
15,030,954 members
Articles / Database Development
Tip/Trick
Posted 26 Jan 2012

Tagged as

Stats

53.2K views
12 bookmarked

How to find a specific string from stored procedures, triggers and functions in database

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
4 Feb 2012CPOL
You can find queries to find a specific string from MSSQL Server
There are several tables and views containing stored procedures, functions and triggers definitions.

Views –
SQL
SYS.SYSCOMMENTS, SYS.SQL_MODULES


Tables –
SQL
INFORMATION_SCHEMA.ROUTINES


I recommend the following query. It shows all SPs and Fns that contain any specific text. If we want to search only procedures, then filter in where clause "AND obj.Type='P'".

SQL
SELECT DISTINCT obj.name AS Object_Name,obj.type_desc 
FROM sys.sql_modules sm INNER JOIN sys.objects obj ON sm.object_id=obj.object_id 
WHERE sm.definition Like '%search text%'


If we want to search specific function or objects names, we can find dependencies using the following system stored procedure:
SQL
EXEC sp_depends @objname =searchobjectname


There is another way to find specific text using INFORMATION_SCHEMA.ROUTINES. But for me, it didn’t give me all the results.
SQL
SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%search text%'
--AND ROUTINE_TYPE='PROCEDURE' (for procedures only)


And also, SYS.SYSCOMMENTS view can have the same usage, but I got duplicates from some results.

SQL
SELECT SO.NAME, SC.TEXT
FROM SYS.SYSOBJECTS SO
JOIN SYS.SYSCOMMENTS SC ON SO.ID = SC.ID
WHERE SC.TEXT LIKE '%search text%' 
–-AND SO.TYPE = 'P'(for procedures only)


Hope this post helps you. Feel free to give your suggestions on this post. Until next post, cheers!!!

[edit]"Treat my content as plain text..." option disabled - OriginalGriff[/edit]

License

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

Share

About the Author

RasikaLB
United States United States
No Biography provided

Comments and Discussions

 
QuestionThanks Pin
dimC10145-Mar-13 1:41
MemberdimC10145-Mar-13 1:41 
GeneralReason for my vote of 5 Very handy while working in the quer... Pin
Pankaj Chamria27-Jan-12 4:13
MemberPankaj Chamria27-Jan-12 4:13 
GeneralRe: u r welcome... Pin
RasikaLB29-Jan-12 21:19
MemberRasikaLB29-Jan-12 21:19 
GeneralSimple, but Handy Pin
yannduran26-Jan-12 17:51
Memberyannduran26-Jan-12 17:51 

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.