Adding (Nolock) After All Select Statements in All Stored Procedures by writing a script in T-SQL

I have many stored procedures in the database and if the stored procedures have select stataments in it, I want to add (Nolock) after all these select statements by writing a script.

I can get all stored procedures by using cursor and sys tables but开发者_如何学Python I didnt figure out how to find exactly select stataments and adding nolock after them.

I need your nice ideas.

Thanks in advance,

Hmmm... I wouldn't do that; adding NOLOCK everywhere is a SQL Anti-Pattern.

There's other ways to achieve the same effect:

1. Adjust Transaction Isolation Level for your applications that call your database, and set them to READ UNCOMMITTED, which would have the same effect as adding NOLOCK to all of your SELECT statements... See: http://msdn.microsoft.com/en-us/library/ms189542.aspx

2. Or implement READ COMMITTED SNAPSHOT mode for the entire database: http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx

In fact, our very own Jeff Atwood wrote about the same issue that you're likely trying to solve here: http://www.codinghorror.com/blog/2008/08/deadlocked.html

If possible, I'd go with option #2, but either of these options will probably be less work up front as well as going forward.