Can I search for a string in a set of stored procedures?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • COHENMARVIN@lycos.com
    #1

    Can I search for a string in a set of stored procedures?

    I found that I'm making an error in my 'case' statements, and now I
    want to go through a database full of many stored procedures to find
    that error in some of them and to fix it. Is there any way to do a
    search through multiple stored procedures at once? I don't see one in
    my version of Sql Server Management Studio?
    Thanks,
    Marv
    Tags: None
    • Ed Murphy
      #2
      Re: Can I search for a string in a set of stored procedures?

      COHENMARVIN@lyc os.com wrote:
      I found that I'm making an error in my 'case' statements, and now I
      want to go through a database full of many stored procedures to find
      that error in some of them and to fix it. Is there any way to do a
      search through multiple stored procedures at once? I don't see one in
      my version of Sql Server Management Studio?
      I think this is right (I mostly still work with 2K which uses older
      methods):

      select o.name
      from sys.objects o
      join sys.sql_modules m on o.object_id = m.object_id
      where m.definition like '%text%'

        Comment

        • nidaar
          #3
          Re: Can I search for a string in a set of stored procedures?

          On Jun 26, 11:08 am, COHENMAR...@lyc os.com wrote:
          I found that I'm making an error in my 'case' statements, and now I
          want to go through a database full of many stored procedures to find
          that error in some of them and to fix it.  Is there any way to do a
          search through multiple stored procedures at once?  I don't see one in
          my version of Sql Server Management Studio?
          Thanks,
          Marv
          Alternatively stored procedures of the database can be scripted by
          right clicking on the database name and selecting Generate Scripts...
          under Tasks in SSMS. This creates a .sql file with all the stored
          procedures (formatted). Then Find/Replace command can be used to
          search / update. Once all changes are made, executing the script can
          update all stored procedures (if "Add If Not Exists..." option is not
          selected during the Generate Scripts Wizard).

          Also "Database Publishing Wizard" can be used (free downloadable from
          Microsoft) to script each stored procedure to individual files. Then a
          program like SQL Server Business Intelligent Development Studio can be
          used to Find/Replace in files. This method can be useful if one also
          wants to add the scripts to source control.

          James

            Comment

            Previous template Next
            Working...