查找“裸”varchar

正则表达式不是解决此问题的正确方法。总会有不可能/极难发现的误报。例如，多行注释块

相反，我建议使用SqlScriptDOM，它是一个 .NET 库，用于解析 T-SQL 语句并与其 Microsoft 提供的抽象语法树进行交互。

然后，您可以使用 .NET 应用程序或 PowerShell 来准确识别丢失的字符大小。

您可以在源代码管理或sys.sql_modules中迭代代码库，并将内容传递给 ScriptDOM 函数。

我从Dan Guzman 的博客借用了大部分代码

# this is the script to parse
$script = @"
-- These are all legal; the regex must not return these

declare @test char(20)

SET @test = CAST (@test AS char     (15 )) /* CHAR( 15 ) is intentional */
DECLARE @test2 char
(
    100
)

RAISERROR ('Char(max) is not valid',16 ,1) WITH nowait
SELECT CHAR(64) AS EmailSeparator -- this char(64) returns @

CREATE TABLE #UglyLegal
(
    a [char](9)
    , b [char] (9)          -- with a space
    , c [char]  (9)         -- with a tab
    , d [char]       (9)    -- tab space tab space
    , e [char]
(9)   
    , characteristic int
    , charge bit
    , chart bit
)


-- These are errors that will be returned
declare @a char;
GO
declare @a char ;      -- a space
GO
declare @a char    ;   -- a tab
GO
declare @a char, @b bit
GO
declare @a char , @b bit
GO
declare @a char = 'lorem'
GO
SELECT cast(9 as char)
GO
SELECT CONVERT(char, 9)

CREATE TABLE #CharFamily
(
    a char(10)
    , b char
    , c nchar(10)
    , d nchar
    , e varchar(MAX)
    , f varchar
    , g nvarchar(10)
    , h nvarchar
)

/* these examples are courtesy of Paul White https://sql.kiwi/ */
DECLARE @foo char varying = 'aaron';
SELECT foo = @foo, lenfoo = LEN(@foo);
SELECT LEN(CAST(REPLICATE('a', 255) AS char varying));
GO
DECLARE @foo "varchar" = 'aaron';
SELECT foo = @foo, lenfoo = LEN(@foo);
SELECT LEN(CAST(REPLICATE('a', 255) AS "varchar"));
"@

try {

    class MyVisitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlConcreteFragmentVisitor {

        [void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.SqlDataTypeReference] $fragment) {
            # Write-Host "$($fragment.SqlDataTypeOption) - size $(($fragment.Parameters[0]).Value) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow

            if (!($fragment.Parameters[0].Value) -and $fragment.SqlDataTypeOption -in ('Char', 'NChar', 'VarChar', 'NVarChar')) {
                Write-Host "Data type $($fragment.SqlDataTypeOption) is missing size at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Red
            }
        }

    }
    
    # Create trusted NuGet package source, if needed
    $packageSource = Get-PackageSource | Where-Object { ($_.Location -EQ "https://www.nuget.org/api/v2") -and ($_.ProviderName -eq "NuGet") -and ($_.IsTrusted -eq $true) }
    if ($packageSource -eq $null) {
        Register-PackageSource NuGetV2 https://www.nuget.org/api/v2 -ProviderName NuGet -Trusted
    }

    # Install package, if needed.
    $tSqlScriptDomPackage = Install-Package Microsoft.SqlServer.TransactSql.ScriptDom -Source ($packageSource.Name) -Scope CurrentUser
    # Get package
    $tSqlScriptDomPackage = Get-Package -Name Microsoft.SqlServer.TransactSql.ScriptDom

    # Load Microsoft.SqlServer.TransactSql.ScriptDom.dll .NET framework assembly into app domain for use in PS scripts
    $tSqlScriptDomPackageFolderPath = [System.IO.Path]::GetDirectoryName($tSqlScriptDomPackage.Source)
    Add-Type -LiteralPath "$tSqlScriptDomPackageFolderPath\lib\net462\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true) # Find the correct compatibility level https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#compatibility_level--160--150--140--130--120--110--100--90--80- */

    # create an ParseError collection for any errors returned by parser
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]

    # create a StringReader for the script for parsing
    $stringReader = New-Object System.IO.StringReader($script)

    # parse the script
    $tSqlFragment = $parser.Parse($stringReader, [ref]$parseErrors)

    # raise an exception if any parsing errors occur
    if ($parseErrors.Count -gt 0) {
        throw "$($parseErrors.Count) parsing error(s): $(($parseErrors | ConvertTo-Json))"
    }
        
    $visitor = [MyVisitor]::new()
    $tSqlFragment.Accept($visitor)

}
catch {
    throw
} 

这里我使用的是TSql150Parser，它根据兼容性级别表匹配 SQL Server 2019

这将是输出

Data type Char is missing size at line 29, column 12, length 4
Data type Char is missing size at line 31, column 12, length 4
Data type Char is missing size at line 33, column 12, length 4
Data type Char is missing size at line 35, column 12, length 4
Data type Char is missing size at line 37, column 12, length 4
Data type Char is missing size at line 39, column 12, length 4
Data type Char is missing size at line 41, column 18, length 4
Data type Char is missing size at line 43, column 16, length 4
Data type Char is missing size at line 48, column 9, length 4
Data type NChar is missing size at line 50, column 9, length 5
Data type VarChar is missing size at line 52, column 9, length 7
Data type NVarChar is missing size at line 54, column 9, length 8
Data type VarChar is missing size at line 58, column 14, length 12
Data type VarChar is missing size at line 60, column 40, length 12
Data type VarChar is missing size at line 62, column 14, length 9
Data type VarChar is missing size at line 64, column 40, length 9

SQL Server Management Studio (SSMS) 允许在搜索中使用正则表达式。我在这方面的技能微乎其微。我有的是这个

  char[^agt\(]]?\s*(?!(\s|\(|\]))

char是有问题的数据类型，后缀为 varchar 和 nvarchar。

[^agt\(]防止匹配上面列出的业务术语。我只包含了当前语料库所需的三个字母。在不同的应用中，这些术语可以被扩展或完全省略。我必须包含左括号（转义为\(），否则char(..会匹配。

]?允许零个或一个右方括号。

\s*允许零个、一个或多个空白字符。

(?!)是一个消极的前瞻。意思是“不跟随”。我真的只想要一个左括号，但必须创建一个( | | )包含空格 ( \s) 和右方 () 的或列表 ( \]) 才能获得所需的匹配。

这主要是由于反复试验造成的。贪婪/懒惰的评估尤其让我失望。我很想得到改进建议。

测试一下：

\bn?(var)?char[]]?\s*\(\d+\)

https://regex101.com/r/bTLmR9/3

附言。应单独检查错误。