Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    11

    Question Unanswered: finding text within a stored procedure

    how can i search for text within stored procedures in SQL Server. I am used to working with Oracle and am unsure how SQL Server handles this.

    So far i have found a system table - SYSCOMMENTS - that appears to hold the text of the stored procedures but this is compressed and therefore my search is not working.

    My goal is to search all stored procedures (about 100) to see which ones use certain tables.

    Cheers
    stu

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: finding text within a stored procedure

    Syscomments contains stored procedure text in the text column. Longer procedures and encrypted ones will require additional effort.

  3. #3
    Join Date
    Nov 2002
    Posts
    11

    Re: finding text within a stored procedure

    Originally posted by DBA
    Syscomments contains stored procedure text in the text column. Longer procedures and encrypted ones will require additional effort.
    thanks DBA, but as you can see from above I had already found this table. Duie to the fact that the stored procedures are compressed i can only see the first 300 or so characters.

    Any other help?

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Post Re: finding text within a stored procedure

    RE:

    thanks DBA, but as you can see from above I had already found this table. Duie to the fact that the stored procedures are compressed i can only see the first 300 or so characters.
    Q1 Any other help?
    A1 Sure, start with sp_helptext (for functionality you need to use directly and / or incorporate).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •