Is there a way to search my stored procedures for a string? 2

[Jerrycurl]

Is there a way to search the text of my stored procedures in SQL 7 (or 6.5 for that matter) for a string?  For example, I want to make sure I'm using "IS NULL" instead of "= NULL" in all my sprocs, so I want to be able to get a list of all the sprocs that have "NULL" in them so I can check them.  Anyone know how to do this?  Thanks.

 

[foxdev]

You can query the TEXT column of the SYSCOMMENTS table looking for the string you desire. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[clawton]

Executing the following statement through Query analyzer gives the names of all procedures containing a text string :<br><br>SELECT name<br>NAME&nbsp;&nbsp;&nbsp;sysobjects, syscomments<br>WHERE&nbsp;&nbsp;syscomments.id = sysobjects.id<br>AND&nbsp;&nbsp;&nbsp;&nbsp;syscomments.text like '%{Insert find string here}%'<br>AND&nbsp;&nbsp;&nbsp;&nbsp;sysobjects.type = 'P'<br><br>Incidently :<br><br>To return the full text of a stored procedure in the DB, the following can be executed through Query Analyzer :<br><br>SELECT text <br>FROM&nbsp;&nbsp;&nbsp;syscomments, sysobjects <br>WHERE&nbsp;&nbsp;name = '{Insert Procedure Name Here}' <br>AND&nbsp;&nbsp;&nbsp;&nbsp;type = 'P' <br>AND&nbsp;&nbsp;&nbsp;&nbsp;syscomments.id = sysobjects.id<br><br>Note that the QA output is by default limited to 256 chars.&nbsp;&nbsp;To increase this, select :<br><br>Query -&gt; Current Connection Options, Advanced Tab and set 'Maximum Chars per column' to something a lot higher.<br><br>Note also that changing the &quot;type = 'P'&quot; line to &quot;type = 'TR'&quot; allows you to look at triggers as well.<br><br>Hope it helps<br><br>Chris.<br><br><br>