如何用PowerShell校验SQL脚本中Volatile Table的创建与删除合规性

核心问题分析 #

你的现有代码存在两个关键问题：

• 全局提取所有文件的创建/删除表名，未按单个脚本文件独立校验，导致跨文件对比无意义
• 正则表达式依赖固定符号（逗号/分号）匹配表名，容易漏判；Compare-Object使用错误，未直接对比数组元素

分步解决方案 #

1. 按单个文件处理，确保同一脚本内校验 #

遍历每个SQL脚本文件，单独提取该文件内的创建/删除语句及对应表名、行号，避免跨文件干扰。

2. 修正正则表达式，精准提取表名 #

使用更鲁棒的正则匹配表名，不依赖语句末尾符号：

• 创建语句：create multiset volatile table\s+(vt_\w+)（忽略大小写，兼容VT_和vt_写法）
• 删除语句：drop table\s+(vt_\w+)（忽略大小写）

完整代码实现 #

$scriptPath = "$packagepath\Scripts\"

# 遍历所有脚本文件
Get-ChildItem $scriptPath -Recurse -Filter *.txt | ForEach-Object {
    $file = $_
    Write-Host "`n=== 正在校验文件: $($file.FullName) ==="

    # 提取所有创建Volatile Table的记录（表名+行号）
    $createMatches = Select-String -Path $file.FullName -Pattern '(?i)create multiset volatile table\s+(vt_\w+)' -AllMatches
    $createTables = $createMatches | ForEach-Object {
        [PSCustomObject]@{
            TableName = $_.Matches.Groups[1].Value.ToLower()
            LineNumber = $_.LineNumber
            Type = 'Create'
        }
    }

    # 提取所有删除Volatile Table的记录（表名+行号）
    $dropMatches = Select-String -Path $file.FullName -Pattern '(?i)drop table\s+(vt_\w+)' -AllMatches
    $dropTables = $dropMatches | ForEach-Object {
        [PSCustomObject]@{
            TableName = $_.Matches.Groups[1].Value.ToLower()
            LineNumber = $_.LineNumber
            Type = 'Drop'
        }
    }

    # 需求1：检查未被删除的Volatile Table
    $allCreateTables = $createTables.TableName
    $allDropTables = $dropTables.TableName
    $missingDrops = $allCreateTables | Where-Object { $_ -notin $allDropTables }
    if ($missingDrops) {
        Write-Host "⚠️ 未找到删除语句的表:"
        $missingDrops | ForEach-Object { Write-Host "  - $_" }
    } else {
        Write-Host "✅ 所有创建的Volatile Table均有对应删除语句"
    }

    # 需求2：检查Drop语句是否在Create语句之后
    $invalidDrops = @()
    foreach ($create in $createTables) {
        $drop = $dropTables | Where-Object { $_.TableName -eq $create.TableName }
        if ($drop) {
            if ($drop.LineNumber -lt $create.LineNumber) {
                $invalidDrops += [PSCustomObject]@{
                    TableName = $create.TableName
                    CreateLine = $create.LineNumber
                    DropLine = $drop.LineNumber
                }
            }
        }
    }
    if ($invalidDrops) {
        Write-Host "⚠️ Drop语句位于Create语句之前的错误:"
        $invalidDrops | ForEach-Object {
            Write-Host "  - 表: $($_.TableName) | 创建行: $($_.CreateLine) | 删除行: $($_.DropLine)"
        }
    } else {
        Write-Host "✅ 所有Drop语句均位于对应Create语句之后"
    }
}

代码说明 #

• 按文件独立处理，确保校验的是同一脚本内的创建与删除关系
• 记录行号用于校验Drop与Create的顺序
• 分别输出两类问题的结果，清晰定位错误

内容的提问来源于stack exchange，提问作者Karel