CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Vinod
Vinod

Reputation: 32911

Search in SP

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.

Upvotes: 2

Views: 298

Answers (7)

Bhushan Bhangale
Bhushan Bhangale

Reputation: 10987

I 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%’

Upvotes: 0

Kristen
Kristen

Reputation: 4311

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 - and then you can use your favourite editor to do a "Find" (and "Replace" if that is appropriate)

Upvotes: 2

jaraics
jaraics

Reputation: 4309

Found this in another SO question, credits go to the original answerer, gbn

SELECT 
    OBJECT_NAME(object_id)
FROM
    sys.sql_modules
WHERE
    definition LIKE '%' + 'WhatIWant' + '%'

It works for objects longer then 4k

Upvotes: 1

Rich Andrews
Rich Andrews

Reputation: 4188

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

Upvotes: 3

Liron Yahdav
Liron Yahdav

Reputation: 10732

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.

Upvotes: 1

Paul Nearney
Paul Nearney

Reputation: 6955

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

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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

Upvotes: 1

Related Questions