Feb 15

Written by: Steve Gray
2/15/2010 4:24 PM 

That was a mouthful.

I have developed a stored procedure that dumps all code from a SQL database into a text file. Sounds boring, but I use it all the time. Really. Case in point – I have performance issues at a client that started out small, and now has table that has millions of records.

I’ll just ignore the comments that ‘I should have anticipated that’, or ‘I should have used scalable coding techniques’. You’re right and I should have. But here I am. I need to retroactively employ scalable coding techniques in a data base that has almost 1000 objects (mostly stored procedures). More specifically, I need to be sure that all the SELECT statements use the (NOLOCK) hint.

 

So, I’m searching my text file for all lines that have ‘FROM’, but don’t have ‘(NOLOCK)’. Also I’ll need lines that have ‘JOIN’ but don’t have ‘(NOLOCK)’.

The solution is to use a text editor that supports REGEX searching, like EditPadPro, and search for this regex:

^((?!nolock).)*from((?!nolock).)*$

Explaining how it works is beyond the scope of this blog entry… but it does.

If you don’t have any of the products from http://www.just-great-software.com/, you’re working too hard.