InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 05:18:35 GMT20http://www.sqlservercentral.com/Forums/Topic636889-1343-1.aspxOne suggestion is to add the [b]upper [/b] to both side of the comparision to make it work with case senstive databases.Ex: where upper(text) like upper(@searchstring)Fri, 04 Jan 2013 10:27:49 GMTSri Kondeti-293259http://www.sqlservercentral.com/Forums/Topic636889-1343-1.aspxI 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[code="sql"]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[/code]Wed, 02 Jun 2010 13:36:28 GMTken.trockhttp://www.sqlservercentral.com/Forums/Topic636889-1343-1.aspxAssuming this post belongs to SQL server 2005, you can use the below query to return all SP in the databaseSELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'Wed, 30 Sep 2009 03:34:12 GMTAbhijit Morehttp://www.sqlservercentral.com/Forums/Topic636889-1343-1.aspxA 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:[url]http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html[/url]Thu, 29 Jan 2009 02:28:21 GMTnigel.http://www.sqlservercentral.com/Forums/Topic636889-1343-1.aspxThis 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.Thu, 29 Jan 2009 02:14:38 GMTnigel.http://www.sqlservercentral.com/Forums/Topic636889-1343-1.aspxI 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.htmlWed, 28 Jan 2009 16:40:21 GMThilahttp://www.sqlservercentral.com/Forums/Topic636889-1343-1.aspxUnfortunately this doesn't quite work, see the explanation in the following article:[url]http://www.sqlservercentral.com/articles/Stored+Procedure/62975/[/url]Tue, 27 Jan 2009 04:22:06 GMTnigel.http://www.sqlservercentral.com/Forums/Topic636889-1343-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Stored+Procedure/65557/">Search All Stored Procedures in a Database</A>[/B]Thu, 15 Jan 2009 01:43:31 GMTGeorge Damien Varkey