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:
Adjust Transaction Isolation Level for your applications that call your database, and set them to
READ UNCOMMITTED
, which would have the same effect as addingNOLOCK
to all of yourSELECT
statements... See: http://msdn.microsoft.com/en-us/library/ms189542.aspxOr 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.
精彩评论