弃用的 raiserror 检测

下面是一个 PowerShell 脚本示例，它使用T-SQL 脚本 DOM 解析器解析指定数据库中所有现有的 T-SQL 模块，并在无法针对目标 SQL 版本解析任何模块时显示错误。这将识别已停用的语法RAISERROR以及任何其他已停用的 T-SQL 语法。

$scriptDomAssemblyPath = "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
$scriptDomNuGetUrl = "https://www.nuget.org/api/v2/package/Microsoft.SqlServer.DacFx.x64/140.3881.1"

# if T-SQL script DOM assembly doesn't already exist at specifed path, download from NuGet and extract assembly from package to $scriptDomAssemblyPath
if(![System.IO.File]::Exists($scriptDomAssemblyPath)) {
    if([String]::IsNullOrWhiteSpace($scriptDomNuGetUrl)) {
        throw "Script DOM assembly not found at $scriptDomAssemblyPath and NuGet package download Url not specified"
    }
    $response = Invoke-WebRequest -Uri $scriptDomNuGetUrl
    if ($response.StatusCode -ne 200) {
        throw "Unable to download Microsoft.SqlServer.TransactSql.ScriptDom NuGet package: $response.StatusCode : $response.StatusDescription"
    }

    $tempZipFilePath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName() + ".zip")
    [System.IO.File]::WriteAllBytes($tempZipFilePath, $response.Content)
    $response.BaseResponse.Dispose()
    $tempUnzipFolderPath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName())
    Expand-Archive -Path $tempZipFilePath -DestinationPath $tempUnzipFolderPath
    $tempZipFilePath | Remove-Item
    Move-Item "$tempUnzipFolderPath\lib\net46\Microsoft.SqlServer.TransactSql.ScriptDom.dll" $scriptDomAssemblyPath
    $tempUnzipFolderPath | Remove-Item -Recurse
}

Add-Type -Path $scriptDomAssemblyPath

# use the appropriate TSqlParser version for target SQL version (TSql120Parser for SQL Server 2014)
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql120Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]

# parse existing T-SQL modules in database
$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI;Application Name=Parse Modules"
$connection = New-object System.Data.SqlClient.SqlConnection($connectionString);
$command = New-object System.Data.SqlClient.SqlCommand("SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName, OBJECT_NAME(object_id) AS ObjectName, definition FROM sys.sql_modules;", $connection);
$connection.Open()
$reader = $command.ExecuteReader()
while ($reader.Read()) {

    $scriptReader = New-Object System.IO.StringReader($reader["definition"])
    $frament = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0) {

        Write-Host "error parsing [$($reader["SchemaName"])].[$($reader["ObjectName"])]"
        foreach ($parseError in $parseErrors) {
            Write-Host "`t$($parseError.Message)"
        }
    }

}
$reader.Close()
$connection.Close()

根据SQL Server 2012 中停用的数据库引擎功能，已弃用的版本raiserror是

RAISERROR integer 'string'.

注意没有括号，后面有一个数字RAISERROR。

使用@HandyD 的方法作为起点，您可以做这样的事情（sqltable模拟返回的内容INFORMATION_SCHEMA.ROUTINES）。

--demo setup
Declare @sqltable table(sqlvar varchar(max))
Declare @CrLf varchar(2) = char(13) + char(10)

insert into @sqltable(sqlvar) values('Raiserror 50000 ''This is old syntax'')')
insert into @sqltable(sqlvar) values('Raiserror 
50000 ''This is old syntax where the command is on multiple lines'')')
insert into @sqltable(sqlvar) values('RAISERROR (''This is new syntax'',16,0)')

--the query
select * from @sqltable where replace(replace(sqlvar,@CrLf,''),' ','') like '%raiserror[0-9]%'

| sqlvar                                                                         |
|--------------------------------------------------------------------------------|
| Raiserror 50000 'This is old syntax')                                          |
| Raiserror   50000 'This is old syntax where the command is on multiple lines') |

使用此脚本来识别使用旧语法的任何过程或函数：

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%RAISERROR%'

这需要在您正在检查的数据库的上下文中执行。对于多个数据库，您需要针对每个数据库运行它。

有关INFORMATION_SCHEMA.ROUTINES的更多信息。