SQL 2012 - replace (nolock) with(nolock)

Question

I need help solving a mismatch problem. The company I work with is migrating from an 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 error and we must include the WITH.

Objects created often have the SELECT declaration where an "alias" is mentioned for the table in question. Ex: Select * from customer as c or Select * from customer c

In these cases to make my problem worse, (NOLOCK) was included between the table and the alias. ex:

Select * form customer `(NOLOCK)` as c

In a mass change, such as REPLACE (NOLOCK) by WITH(NOLOCK), it will stay that way:

Select * from customer `WITH(NOLOCK)` as c

Error in execution/compilation.

But how to do this in a "fast" way when we have more than 900 objects among those cited above?

Answer 1

Attention: Make a backup before your database. Ideally run this on a test basis.

By SQL Management Studio graphical interface:

• Right-click on the database you want;
• Select the option Generate Scripts.
• Go ahead and select which object types you want to generate the script for.
• On the screen where it appears as you want to export, click the Advanced button and check the option DROP and CREATE script for the same name option.

• Export;

• Once 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.

Like I said, run tests before you run production. If you can’t do it on a test basis, then just export two stored procedures and see if it works.

Updating

The option DROP and CREATE script ensures that a DROP will be generated for each object, because only by DROPPING 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 if the script generator will write to the file respecting the dependencies between the objects. This is important because you cannot drop a Function that is being used in a Stored Procedure. Thus, it is necessary first to drop the SP, then to drop the Function.

Update 2 (As per question update):

Regular expression in Notepad++ version 6.4.5

In Notepad++ there is the option to find and replace text by regular expression. To do this, press CTRL-F and use Regular Expression:

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

Testing:

In the first step, this:

Select * from QUOTE (NOLOCK) AS TB

It is transformed into that:

Select * from QUOTE WITH (NOLOCK) AS TB

That in the second step is transformed into that:

Select * from QUOTE AS TB WITH (NOLOCK)

Explanation of Regular Expression:

(WITH\s+\(NOLOCK\))\s+(AS\s+[A-Z]+)

First we’ll break it into three pieces:

(WITH\s+\(NOLOCK\))
\s+
(AS\s+[A-Z]+)

Note that the 1st and 3rd pieces are delimited by paranteses. The 1st piece is called 1 and the 3rd piece is called 2. Record this.

1st piece:

WITH
\s+
\(NOLOCK\)

This piece is looking for the WITH string anywhere on the line. After encountering, as many whitespace spaces as there are ( s+) until you find the string (NOLOCK). Inverted bars are necessary to escape the paranteses character, since it is a reserved character.

2nd piece:

The 2nd piece ( s+) is just a way of traversing all the existing spaces between (NOLOCK) and the word AS.

3rd piece:

AS
\s+
[A-Z]+

The 3rd piece meets the word AS that must necessarily come after 1 or several blank spaces (since it comes after the 2nd piece).

Once this is done, one or more white spaces are covered until you find a word that has one or more characters (from A-Z). This is the name of the table that comes after AS.

Substituting

As explained above, the regular expression is divided into two groups (the 1st and 3rd pieces). Therefore, the replacement is simply rewrite the 3rd piece + a blank + 1st piece, like this:

\2 \1