finding text within a stored procedure

yalnik · #1 (**permalink**) 12-03-02, 18:37

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

DBA · #2 (**permalink**) 12-03-02, 23:59

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

yalnik · #3 (**permalink**) 12-05-02, 18:07

Quote:

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?

DBA · #4 (**permalink**) 12-06-02, 02:55

RE:

Quote:

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).

Internet Services

Web Development

Digital Marketing

Consumer Tech