PATINDEX - Pattern has this or doesn't

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PATINDEX - Pattern has this or doesn't

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-06-15 : 13:49:34

Im trying to use one PATINDEX for all possibilities.

Text

1) FROM dbo.tablename
2) FROM
     tablename
3) FROM tablename
4) FROM
     dbo.tablename

Pattern so far

PATINDEX('%FROM[^A-Za-z@]' + @DependentObjectName + '[^A-Za-z@]%', @ObjectDefinition)

Would also like to include 'JOIN' as well. I am trying to write a script to insert 'WITH (NOLOCK)' on tables where it isn't already.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-15 : 14:24:09

Oh my!

Why oh why would you want to add "with (nolock)" to everything!? Wow!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-15 : 14:32:46

From someone who's done this:

- Good luck re: PATINDEX. I tried, failed, started looking at non-database careers. It's your sanity, treasure it.
- Don't do it, even if you figure it out. My deepest professional embarrassment is that I added NOLOCK (under duress) to every query. (also added ROWLOCK to all UPDATE/INSERT/DELETE) Hasn't solved a single performance problem, probably has created a few new ones.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-15 : 14:37:59