January 15, 2009 at 1:43 am
Comments posted to this topic are about the item Search All Stored Procedures in a Database
January 27, 2009 at 4:22 am
Unfortunately this doesn't quite work, see the explanation in the following article:
http://www.sqlservercentral.com/articles/Stored+Procedure/62975/
January 28, 2009 at 4:40 pm
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
January 29, 2009 at 2:14 am
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.
January 29, 2009 at 2:28 am
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
September 30, 2009 at 3:34 am
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
June 2, 2010 at 1:36 pm
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.NameFROM ' + @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
January 4, 2013 at 10:27 am
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)
May 2, 2016 at 4:15 pm
Thanks for the script.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply