Create a script to search for a missing word or a phrase
-
Hi all,
We compulsorily use NOLOCK in our scripts on reporting DB and development generally forget putting NOLOCKS or schema names with tables.
Can anybody guide me in creating a script to search for a missing word or a phrase in SQL Server script.
Our format is
<schema>.<table> <alias here> WITH NOLOCK <or alias here>
or
<schema>.<table> <alias here> NOLOCK <or alias here>
or
<schema>.<table> <alias here> (NOLOCK) <or alias here>
but generally developers write only <table> <alias>
Any help will be appreciated.
Regards
-
BrainDonor (1/16/2015)
Can I ask first of all why is the use of NOLOCK compulsory, and are you aware of the potential problems that such a strict rule can cause?Yes we are well aware of what is NOLOCK and why we are using it. We do not use it everywhere but wherever we use it, it has to be there. And that's why I need a script to monitor it.
-
i can't help with the nolock, but i bought a plugin for SSMS called SQL Enlight, which does check scripts for semi colons,fully qualified schema names, aliases and more; this app may allow you to create a rule of your own, though. i'm sure there are other applications that do the same as well.
i bought one because i went down the development path of doing it myself, and found it to be bigger project than i thought, evne using the dlls available to tokenize a query. it was cheaper for me to buy it than to pour X hours creating it myself.
admittedly, i rarely use this application, since my personal workflow is to always add schema name and aliases anyway, but it's nice to check a large batch of other develoeprs work with it sometimes.
i'd benefit from something that spell checked all my @Varibels and alaiases instead 😛
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
T.Ashish (1/16/2015)
Hi all,We compulsorily use NOLOCK in our scripts on reporting DB and development generally forget putting NOLOCKS or schema names with tables.
Can anybody guide me in creating a script to search for a missing word or a phrase in SQL Server script.
Our format is
<schema>.<table> <alias here> WITH NOLOCK <or alias here>
or
<schema>.<table> <alias here> NOLOCK <or alias here>
or
<schema>.<table> <alias here> (NOLOCK) <or alias here>
but generally developers write only <table> <alias>
Any help will be appreciated.
Regards
You could set the isolation level at the beginning of any stored procedures you use and, perhaps, within any scripts you may be using. I just advise against doing anything like that or NOLOCK because the very place where you need it the most to "avoid blocking" are the places that also have the highest risk of producing duplicate or incorrect data.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply