Question:
I need help resolving an incompatibility issue. The company I work for is migrating a SQL Server 2008 database to 2012, but we have identified some problems.
The main one is the declaration of (NOLOCK) without the WITH in VIEWs, FUNCs and PROCs, when using linked server, it presents an error and we must include the WITH .
The objects created, many times, have the SELECT statement where an "alias" for the table in question is mentioned. Ex: Select * from customer as c or Select * from customer c
In these cases to make my problem worse, the (NOLOCK) was included between the table and the alias. ex:
Select * form customer `(NOLOCK)` as c
In a mass change, like REPLACE (NOLOCK) by WITH(NOLOCK), it will look like this:
Select * from customer `WITH(NOLOCK)` as c
Showing error in execution/compilation.
But how to do this in a "quick" way when we have more than 900 objects among those mentioned above?
Answer:
Attention: Make a backup before your database . Ideally, run this on a test basis.
Through the SQL Management Studio graphical interface:
- Right-click on the database you want;
- Select the Generate Scripts option.
- Go ahead and select which object types you want to script.
- On the screen where how you want to export appears, click on the Advanced button and check the Script DROP and CREATE option for the option with the same name.
-
Perform the export;
-
Once that's done, open the generated file and with an editor like Notepad++ replace (NOLOCK) with WITH (NOLOCK)
- Load this file into SQL Management Studio and run it.
As I said, do tests before running in production. If you can't do it on a test basis then export just two stored procedures and see if it works.
Update
The Script DROP and CREATE option guarantees that a DROP will be generated for each object, as only by DROPing and creating again will you be able to perform this operation. You could use ALTER, but there is no such option in the script generator (at least in my version).
Another thing you need to check is whether the script generator will write to the file respecting dependencies between objects. This is important, as you cannot drop a function that is being used in a Stored Procedure. Thus, it is necessary to drop the SP beforehand, and then drop the Function.
Update 2 (As per the question update):
Regular expression in Notepad++ version 6.4.5
In Notepad++ there is the option to find and replace a text by regular expression. To do this, press CTRL-F and use the Regular Expression option:
1st step
Look for it:
\(NOLOCK\)
Replace with this:
\1WITH \(NOLOCK\)
2nd step
Look for it:
(WITH\s+\(NOLOCK\))\s+(AS\s+[A-Z]+)
Replace with this:
\2 \1
Test :
In the 1st step, this:
Select * from QUOTE (NOLOCK) AS TB
It's turned into this:
Select * from QUOTE WITH (NOLOCK) AS TB
Which in the 2nd step is transformed into this:
Select * from QUOTE AS TB WITH (NOLOCK)
Explanation about Regular Expression :
(WITH\s+\(NOLOCK\))\s+(AS\s+[A-Z]+)
First let's break it into three pieces:
(WITH\s+\(NOLOCK\))
\s+
(AS\s+[A-Z]+)
Note that the 1st and 3rd pieces are delimited by parentheses. The 1st chunk is called \1 and the 3rd chunk is called \2. Record this.
1st piece:
WITH
\s+
\(NOLOCK\)
This chunk is looking for the WITH string anywhere on the line. After finding, it consumes how many whitespaces there are (\s+) until finding the string (NOLOCK). Backslashes are necessary to escape the parenthesis character, as it is a reserved character.
2nd piece:
The 2nd piece (\s+) is just a way of traversing all the spaces between (NOLOCK) and the word AS.
3rd piece:
AS
\s+
[A-Z]+
The 3rd chunk finds the word AS which must necessarily come after 1 or several blank spaces (since it comes after the 2nd chunk).
Once this is done, one or more blank spaces are traversed until a word with one or more characters (from AZ) is found. This is the name of the table that comes after the AS.
Replacement
As explained above, the regular expression is divided into two groups (the 1st and 3rd pieces). So the replacement is simply rewriting the 3rd chunk + a blank + 1st chunk, like this:
\2 \1