• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

Searching for a keyword in stored procedures of MS SQL 2005

Hello,

I need to find and update certain keywords in all stored procedures of MS SQL 2005 ( Std/Ent ).
Is there a built-in or 3rd party tool to search for keywords?

Thank you for your help
0
JOSHUABT
Asked:
JOSHUABT
  • 2
2 Solutions
 
Kent DyerIT Security Analyst SeniorCommented:
Nah..  SPROCS are just SQL Tables too..  They are just System tables though..

http://blog.sqlauthority.com/2007/11/27/sql-server-2005-list-all-stored-procedure-in-database/

HTH,

Kent
0
 
cyberkiwiCommented:
Say your keywords is "mykey", use this query

select object_name(object_id), *
from sys.sql_modules
where definition like '%mykey%'
0
 
cyberkiwiCommented:
To replace it though, you need to modify each sproc individually and do a search and replace from SSMS then execute the ALTER PROCEDURE statements.
0
 
Anthony PerkinsCommented:
>>Is there a built-in or 3rd party tool to search for keywords?<<
I strongly recommend Red-Gate's SQL Search.  It is a free SSMS addin that you can download from here:
http://www.red-gate.com/products/sql-development/sql-search/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now