Search in SP

Question

Hi,

I am actually having 100's of SP in my database. I have to find a set of 10 SP's in that which have a particular comment inside them. Is there any search query for this.

Answer 1

Found this article which does exactly what your after if your using SQL Server

I'll certainly be keeping a copy of this code for my own use :)

Also, It doesn't just work for comments but it appears to work for all text in an SP.

Edit

I've included a copy of the code for simplicity but all credit goes to Les Smith

CREATE PROCEDURE Find_Text_In_SP
@StringToSearch varchar(100) 
AS 
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name
GO

Answer 2

Don't remember the exact tables names but it would be something like this. Give it a try.

SELECT sysobjects.name FROM syscomments, sysobjects WHERE syscomments.id=sysobjects.id WHERE syscomments.TEXT LIKE ‘%particular comment%’

Answer 3

select OBJECT_NAME(id), * from syscomments WHERE text LIKE 'WhateverYouAreSearchingFor'

Answer 4

you can use the information_schema for this:

select *
from information_schema.routines
where routine_definition like '%your comment to find%'

Bear in mind that this only looks through the first 4096 characters (i believe...) - to search through more characters, you have to query the underlying system tables directly. Have a look at the definition of the information_schema.routines view to see which system tables it is querying

Edit: @Rich's answer is what you will need if you need to query more than 4096 characters

Answer 5

I'm not sure if this is the best solution, but the following should work (although it may be slow):

1. Open the DB in SQL Server Management Studio.
2. In the Object Explorer, right click the DB, Tasks > Generate Scripts.
3. Click the Next button 3 times until you get to the Choose Object Types step.
4. Check Stored procedures and click Next.
5. Click Select All to select all sp's or if you can limit the sp's somehow, choose just those you want to search in.
6. Click Finish and then Finish again.
7. Click Close.
8. You should now have all of your sp's scripted, so you can search through them.

Answer 6

Note that the syscomments search methods will fail if the String Search spans the boundary of records in syscomments.

To be 100% I guess you will have to Script the Sprocs out of the database

Suggest considering storing each Sproc in a separate file (and store in revision control repository etc.) rather than just Altering them in-situ in the DB