添加关键字提示

Question

我正在寻找编写一个PowerShell脚本，该脚本将遍历SQL脚本文件的内容，查找包含TABLE提示的行，其中没有WITH语句。

例子：

• SELECT * FROM dbo.Table (NOLOCK)
• SELECT * FROM dbo.Table (INDEX=PK_Table)
• SELECT * FROM dbo.Table (NOLOCK,INDEX=PK_Table)
• SELECT * FROM dbo.Table (INDEX=PK_Table,NOLOCK)

我想编写一个PowerShell脚本看起来对于那些提示并在TABLE HINT之前添加WITH。

所以它们看起来像下面这样：

• SELECT * FROM dbo.Table WITH(NOLOCK)
• SELECT * FROM dbo.Table WITH(INDEX=PK_Table)
• SELECT * FROM dbo.Table WITH(NOLOCK,INDEX=PK_Table)
• SELECT * FROM dbo.Table WITH(INDEX=PK_Table,NOLOCK)

到目前为止，我有以下几点：

foreach ($str in Get-Content "d:\script.sql") { 
    if ([regex]::IsMatch($str, "\({0,1}NOLOCK\)|\(,NOLOCK\{0,1}\)","IgnoreCase")) { 
     $strReplace = [regex]::Replace($str, "\({0,1}NOLOCK\)|\(,NOLOCK\{0,1}\)", "WITH $1") 
     write $strReplace 
    } 
} 

但是，它不插入WITH到行中并保持匹配和行。

这也将不得不处理脚本如下所示：

SELECT Table1.*, Table2.* FROM dbo.Table1 Table1 (NOLOCK) 
INNER JOIN dbo.Table2 Table2 (INDEX=PK_Table2, NOLOCK) 
ON (Table2.Id = Table1.Id) 

下面的建议

：我用下面的：

$expr = "(?=\([^)]*(?=(NOLOCK|INDEX=|INDEX\())[^)]*\))(?<!WITH\s+)" 
 

 
if ([regex]::IsMatch($str, "(?=\([^)]*(?=(NOLOCK|INDEX=|INDEX\())[^)]*\))","IgnoreCase")) { 
 
     $replaced = $str -replace $expr,"WITH "

Answer 1

以下expession会工作，至少在你的输入样本中。没有保证，这将

• 匹配所有的事情应该
• 比赛没有任何的东西不应该
• 没有造成严重破坏你的SQL代码

您需要对所有三种情况进行非常仔细的测试也想到边缘情况。

$sample = " 
SELECT * FROM dbo.Table (NOLOCK) 
SELECT * FROM dbo.Table (INDEX=PK_Table) 
SELECT * FROM dbo.Table (NOLOCK,INDEX=PK_Table) 
SELECT * FROM dbo.Table (INDEX=PK_Table,NOLOCK) 


SELECT * FROM dbo.Table WITH (NOLOCK) 
SELECT * FROM dbo.Table WITH (INDEX=PK_Table) 
SELECT * FROM dbo.Table WITH (NOLOCK,INDEX=PK_Table) 
SELECT * FROM dbo.Table WITH (INDEX=PK_Table,NOLOCK) 
" 

$expr = "(?=\([^()]*(?=\b(?:NOLOCK|INDEX)\b)[^()]*\))(?<!WITH\s+)" 

$sample -replace $expr,"WITH " 

结果：

 
SELECT * FROM dbo.Table WITH (NOLOCK) 
SELECT * FROM dbo.Table WITH (INDEX=PK_Table) 
SELECT * FROM dbo.Table WITH (NOLOCK,INDEX=PK_Table) 
SELECT * FROM dbo.Table WITH (INDEX=PK_Table,NOLOCK) 


SELECT * FROM dbo.Table WITH (NOLOCK) 
SELECT * FROM dbo.Table WITH (INDEX=PK_Table) 
SELECT * FROM dbo.Table WITH (NOLOCK,INDEX=PK_Table) 
SELECT * FROM dbo.Table WITH (INDEX=PK_Table,NOLOCK) 

正则表达式的高级击穿：

(?=\([^()]*(?=\b(?:NOLOCK|INDEX)\b)[^()]*\)) # a position followed by the words "INDEX" or 
               # "NOLOCK" somewhere in parentheses 
               # (add more keywords if you want) 
(?<!WITH\s+)         # the same position must not be preceded by "WITH" 

这两个环视条件下准确地确定与关键字缺少的位置。之后插入它与-replace运算符很简单。

详细击穿：

(?=      # start zero-width look-ahead 
    \(     # "(" 
    [^()]*     # any character except "(" and ")", repeat 
    \b      # a word boundary 
    (?=     # start non-matching group 
    (?:NOLOCK|INDEX)  #  "NOLOCK" or "INDEX" 
)      # end group 
    \b      # a word boundary 
    [^()]*     # any character except "(" and ")", repeat 
    \)      # ")" 
)      # end look-ahead 
(?<!      # start zero-width negative look-behind 
    WITH     # "WITH" 
    \s+     # any number of whitespace 
)      # end look-begind 

---

话虽这么说，你的脚本可以更优雅写成

(Get-Content -Raw D:\script.sql) -replace $expr,"WITH " 

你也可以将输出重定向到一个新文件

(Get-Content -Raw D:\script.sql) -replace $expr,"WITH " > script_processed.sql 

The Raw parameters ter将文件作为一个大字符串读取，而不是按行读取。在这种情况下，逐行处理将不会有用。