在SSIS.dtsx包中查找表名

我正在嘗試掃描 SSIS .dtsx 包以獲取表名。是的，我知道我應該使用 [xml] 和決議 SQL 語言的工具。這在這個時候似乎是不可能的。PowerShell 可以理解 [xml]，但 SQL 決議器通常會花費 并且使用 ANTLR 是一專案前無法接受的投資。我對建議持開放態度，但我不是在要求工具推薦。

有兩（2）個問題。

1) `&.;` does not appear to be recognized as separate from the table name capture item
2) TABLE5 does not appear to be found

是的，我也知道架構名稱不應該硬編碼到源代碼中。這使得 DBA 難以/不可能管理資料庫。這就是這里的做法。

我怎樣才能讓正則運算式&.*;從捕獲中省略并識別 dbo.TABLE5

這是我用來掃描 .dtsx 檔案的代碼。

PS C:\src\sql> Get-Content .\Find-FromJoinSql.ps1
Get-ChildItem -File -Filter '*.dtsx' |
    ForEach-Object {
        $Filename = $_.Name
        Select-String -Pattern '(FROM|JOIN)(\s|&.*;) (\S )(\s|&.*;) ' -Path $_ -AllMatches |
        ForEach-Object {
            if ($_.Matches.Groups.captures[3].value -match 'dbo') {
                "$Filename === $($_.Matches.Groups.captures[3].value)"
            }
        }
    }

這是 .dtsx 檔案中文本型別的一個小示例。

PS C:\src\sql> Get-Content .\sls_test.dtsx
USE ADATABASE;
SELECT * FROM dbo.TABLE1 WHERE F1 = 3;
SELECT * FROM dbo.TABLE2 T2
    FULL OUTER JOIN dbo.TABLEJ TJ
        ON T2.KEY = TJ.KEY;
SELECT * FROM dbo.TABLE3 T3
    INNER JOIN ADATABASE2.dbo.TABLEK
TK ON
T3.user_id = TK.user_id

SELECT * FROM dbo.TABLE4 T4 FULL OUTER JOIN dbo.TABLE5 T5
    ON T4.F1 = T5.F1;
EXIT

在此資料上運行腳本會產生：

PS C:\src\sql> .\Find-FromJoinSql.ps1
sls_test.dtsx === dbo.TABLE1
sls_test.dtsx === dbo.TABLE2
sls_test.dtsx === dbo.TABLEJ
sls_test.dtsx === dbo.TABLE3
sls_test.dtsx === ADATABASE2.dbo.TABLEK
TK
sls_test.dtsx === dbo.TABLE4

PS C:\src\sql> $PSVersionTable.PSVersion.ToString()
7.1.5

uj5u.com熱心網友回復：


在這些檔案中沒有替換某些物體 ( )確實很奇怪。

稍微更改正則運算式模式以捕獲如下所示的 dbo.table 名稱。

使用獲取內容

$regex = [regex] '(?im)(?:FROM|JOIN)(?:\s|&[^;] ;) ([^\s&] )(?:\s|&[^;] ;)*'
Get-ChildItem -Path D:\Test -File -Filter '*.dtsx' |
    ForEach-Object {
        $match = $regex.Match((Get-Content -Path $_.FullName -Raw))
        while ($match.Success) {
            "$($_.Name) === $($match.Groups[1].Value)"
            $match = $match.NextMatch()
        } 
    }

使用選擇字串

至于為什么Select-String -AllMatches跳過你的Table5。
來自檔案：“當 Select-String 在一行文本中找到多個匹配項時，它仍然只為該行發出一個 MatchInfo 物件，但該物件的 Matches 屬性包含所有匹配項。”

這意味著您需要另一個回圈來從每個 $MatchInfo 物件中獲取所有 $Matches 以在您的輸出中獲取它們：

$pattern = '(?:FROM|JOIN)(?:\s|&[^;] ;) ([^\s&] )(?:\s|&[^;] ;)*'
Get-ChildItem -Path 'D:\Test' -File -Filter '*.dtsx' |
    ForEach-Object {
        $Filename = $_.Name
        Select-String -Pattern $pattern -Path $_.FullName -AllMatches |
        ForEach-Object {
            # loop again, because each $MatchInfo object may contain multiple
            # $Matches objects if more matches were found in the same line
            foreach ($match in $_.Matches) {
                if ($match.Groups[1].value -match 'dbo') {
                    "$Filename === $($match.Groups[1].value)"
                }
            }
        }
    }

輸出：

sls_test.dtsx === dbo.TABLE1
sls_test.dtsx === dbo.TABLE2
sls_test.dtsx === dbo.TABLEJ
sls_test.dtsx === dbo.TABLE3
sls_test.dtsx === ADATABASE2.dbo.TABLEK
sls_test.dtsx === dbo.TABLE4
sls_test.dtsx === dbo.TABLE5

正則運算式詳細資訊：

(?im)              Use case-insensitive matching and have '^' and '$' match at linebreaks
(?:                Match the regular expression below
                   Match either the regular expression below (attempting the next alternative only if this one fails)
      FROM         Match the characters “FROM” literally
   |               Or match regular expression number 2 below (the entire group fails if this one fails to match)
      JOIN         Match the characters “JOIN” literally
)                 
(?:                Match the regular expression below
   |               Match either the regular expression below (attempting the next alternative only if this one fails)
      \s           Match a single character that is a “whitespace character” (spaces, tabs, line breaks, etc.)
   |               Or match regular expression number 2 below (the entire group fails if this one fails to match)
      &            Match the character “&” literally
      [^;]         Match any character that is NOT a “;”
                   Between one and unlimited times, as many times as possible, giving back as needed (greedy)
      ;            Match the character “;” literally
)                  Between one and unlimited times, as many times as possible, giving back as needed (greedy)
(                  Match the regular expression below and capture its match into backreference number 1
   [^\s&]          Match a single character NOT present in the list below
                   A whitespace character (spaces, tabs, line breaks, etc.)
                   The character “&”
                   Between one and unlimited times, as many times as possible, giving back as needed (greedy)
)                 
(?:                Match the regular expression below
   |               Match either the regular expression below (attempting the next alternative only if this one fails)
      \s           Match a single character that is a “whitespace character” (spaces, tabs, line breaks, etc.)
   |               Or match regular expression number 2 below (the entire group fails if this one fails to match)
      &            Match the character “&” literally
      [^;]         Match any character that is NOT a “;”
                   Between one and unlimited times, as many times as possible, giving back as needed (greedy)
      ;            Match the character “;” literally
)*                 Between zero and unlimited times, as many times as possible, giving back as needed (greedy)

標籤：sql sql-server 正则表达式 powershell ssis

上一篇：為什么不從條件中出現Write-Verbose訊息

下一篇：獲取DNS腳本在報告中缺少輸出