Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Search All Stored Procedures in a Database Expand / Collapse
Author
Message
Posted Thursday, January 15, 2009 1:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 21, 2011 9:47 AM
Points: 16, Visits: 47
Comments posted to this topic are about the item Search All Stored Procedures in a Database
Post #636889
Posted Tuesday, January 27, 2009 4:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:43 AM
Points: 1,184, Visits: 2,680
Unfortunately this doesn't quite work, see the explanation in the following article:

http://www.sqlservercentral.com/articles/Stored+Procedure/62975/


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #644033
Posted Wednesday, January 28, 2009 4:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:29 PM
Points: 25, Visits: 83
I use the following piece of code for a while now and with great success:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%text%' AND ROUTINE_TYPE = 'PROCEDURE'



http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
Post #645505
Posted Thursday, January 29, 2009 2:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:43 AM
Points: 1,184, Visits: 2,680
This still suffers from the same problem, in that the ROUTINE_DEFINITION column is only 4000 characters long. Any stored proc longer than 4000 characters in length is split over two rows and any text that spans this 'split' in the definition will not be found by your method. See the link posted earlier.

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #645669
Posted Thursday, January 29, 2009 2:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:43 AM
Points: 1,184, Visits: 2,680
A slight correction :). Having looked at this further it seems that the INFORMATION_SCHEMA.ROUTINES view does not split the procedure definition over more than 1 row it in fact only shows the first 4000 characters of any stored proc. Which actually makes using this view for searching the procedure definitinion less than useful.

Oh, and the INFORMATION_SCHEMA views are only in 2005 and above.

For 2005 and above the take a look at the OBJECT_DEFINITION function, mentinoed in the following article on the same subject:
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #645676
Posted Wednesday, September 30, 2009 3:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:36 AM
Points: 924, Visits: 714
Assuming this post belongs to SQL server 2005, you can use the below query to return all SP in the database

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'


Abhijit - http://abhijitmore.wordpress.com
Post #795608
Posted Wednesday, June 2, 2010 1:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:29 PM
Points: 323, Visits: 1,475
I added a quick option to George's original block to specify the DB you want to search. This way it can sit in Master and be called from anywhere on the server.

But this split at character 4000 in syscomments is an eye opener. Means this solution isn't complete, even for searching procs only.

Ken


ALTER proc [dbo].[SearchObjects] 
@database varchar(100),
@searchString varchar(100)
As
Declare @Sql nvarchar(1000)

Set @Sql = 'SELECT Distinct SO.Name FROM ' + @database + '.dbo.' + 'sysobjects SO (NOLOCK) ' +
'INNER JOIN ' + @database + '.dbo.' + 'syscomments SC (NOLOCK) on SO.Id = SC.ID ' +
'AND SO.Type = ''P'' AND SC.Text LIKE ''%' + @searchString + '%'' ORDER BY SO.Name'

Exec sp_executesql @Sql


Post #931639
Posted Friday, January 4, 2013 10:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 30, 2014 11:32 AM
Points: 1, Visits: 20
One suggestion is to add the upper to both side of the comparision to make it work with case senstive databases.

Ex: where upper(text) like upper(@searchstring)
Post #1403000
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse