search for a key word in all the Stored Proc's for a given DB?

Question

How to do a global search for a key word in all the Stored Proc's for a given DB?

I used the following but I am unable to get the desired results...

SELECT name 
FROM sysobjects 
WHERE id IN 
  ( 
    SELECT id 
    FROM syscolumns 
    WHERE name = 'loadStatus' COLLATE SQL_Latin1_General_CP1_CI_AS 
  ) 

SELECT name 
FROM sysobjects 
WHERE id IN 
  ( 
    SELECT id 
    FROM syscolumns 
    WHERE name like '%loadStatus%' COLLATE SQL_Latin1_General_CP1_CI_AS
  )

Regards -Vas

score 3 · Answer 1 · answered Sep 01 '09 at 21:13

3

Only sys.sql_modules contains the whole definition of the procedure. Other views like INFORMATION_SCHEMA.ROUTINES contain a truncated definition.

select object_name(o.object_id), m.*
from sys.sql_modules m
join sys.objects o on m.object_id = o.object_id
where o.type= 'P'
and m.definition like N'%<keyword>%'

answered Sep 01 '09 at 21:13

Remus Rusanu

288,378
40
442
569

Didn't know about the 4000 character limit on INFORMATION_SCHEMA.ROUTINES. – OMG Ponies Sep 01 '09 at 21:17
Msg 208, Level 16, State 1, Line 2 Hi Remus! When running the script getting the error :- Invalid object name 'sys.sql_modules'. Msg 208, Level 16, State 1, Line 2 Invalid object name 'sys.objects'. The script was select object_name(o.object_id), m.* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id where o.type= 'P' and m.definition like N'%GetExcpCTSInfo%' – vas Sep 01 '09 at 21:48
You did mark your question sql-server-2005. Looks like you're testing on SQL 2000. – Remus Rusanu Sep 01 '09 at 21:56
You realize I did not hack up MSDN and put in place the documentation of those metadata views. Check @@version, then check if you have some minimal rights in the database (VIEW DEFINITION). – Remus Rusanu Sep 01 '09 at 22:40

score 2 · Accepted Answer · edited Sep 01 '09 at 21:12

2

Have a look at this link - http://www.sqlservercentral.com/scripts/T-SQL+Aids/31131/ and see if that helps.

The SP in the linked article was written by Prasad Bhogadi and is as follows:

CREATE PROCEDURE SEARCHFORSTRING @SEARCHSTRING VARCHAR(100)
AS
            SELECT  DISTINCT(sysobjects.name)
                FROM 
                sysobjects,syscomments
            WHERE 
                sysobjects.id =     syscomments.id
            AND 
                sysobjects.type = 'P'
            AND 
                sysobjects.category=0
AND
        CHARINDEX(@SEARCHSTRING ,syscomments.text)>0

You can use this to create an SP in your database or adapt it for a one-time query.

edited Sep 01 '09 at 21:12

KM.

101,727
34
178
212

answered Sep 01 '09 at 21:05

TLiebe

7,913
1
23
28

Thanks to KM for fixing up the SQL code and making it look presentable. – TLiebe Sep 01 '09 at 21:13
heh - tonnes of whitespace, but none for the CHARINDEX comparison – OMG Ponies Sep 01 '09 at 21:15
@rexem, all I did was to highlight the code block and click on the 1s & 0s icon to make the text fixed width, I'm not going to fix the white space and indents... – KM. Sep 01 '09 at 21:18

search for a key word in all the Stored Proc's for a given DB?

2 Answers2

Linked