Query all Stored Procedures

[Steffi1013]

I am looking for a way to query all my stored procedures in a certain database to find out if the string "set transaction isolation level read uncommitted" has been put in them... does anyone have an easy way to do this?

 

[markros]

Yes, slightly adjust the code from this blog:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-script-all-stored-procedures-in-a

PluralSight Learning Library

 

[Steffi1013]

I am a little confused as to what you are suggesting? Are you recommending I script out all the procedures before searching for that string inside the procedure?

 

[Imakeoil]

Hi Steffi,

This works on my SQL 2000 server.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%"set transaction isolation level read uncommitted%'
AND ROUTINE_TYPE='PROCEDURE'

Hope this helps.

 

[markros]

No, I meant the code similar to Imakeoil (e.g. add LIKE clause to the query).

PluralSight Learning Library

 

[Steffi1013]

Thanks everyone! This gave me what I needed.

 

[philhege]

Late to the party, but:

Be careful with evaluating ROUTINE_DEFINITION. In your case, Steffi, it shouldn't be a problem because the SET statement should be at the beginning of the routine.

Routines larger than 8KB are split into records in the syscomments table. Take a look at

http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

for an explanation and a way to retrieve large SP text.

-------++NO CARRIER++-------