Click here to Skip to main content
Click here to Skip to main content

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

, 4 Feb 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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 –
SYS.SYSCOMMENTS, SYS.SQL_MODULES
 
Tables –
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'".
 
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:
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.
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.
 
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 PinmemberdimC10145-Mar-13 2:41 
GeneralReason for my vote of 5 Very handy while working in the quer... PinmemberPankaj Chamria27-Jan-12 5:13 
GeneralRe: u r welcome... PinmemberRasikaLB29-Jan-12 22:19 
GeneralSimple, but Handy Pinmemberyannduran26-Jan-12 18:51 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411023.1 | Last Updated 4 Feb 2012
Article Copyright 2012 by RasikaLB
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid