【SQL Server】任意の数のsqlファイル(SELECT)を実行してCSVを出力するPowerShellスクリプト

背景・目的

仕事でシステム開発を行う際、DBの更新値の比較作業を行うことが多々あります。
(モジュールの修正前後、環境設定の違い、etcetc・・・)

そんなときは、比較したいレコードをそれぞれテキスト出力してDiffツールで比較を行うということをやっています。

使っているDBMSは「SQL Server」です。SQL Serverの場合、付属の統合管理ツール「Management Studio」の「結果をファイルに出力」機能でテキスト出力が可能です。

しかし、一々GUI上でSQLを編集して、ボタンを押して、保存先とファイル名を指定して・・・とやるのはあまり効率の良い作業になるとは言えません。テーブルごとに出力するにはその分操作しないといけないですしね。

そこで、効率よく一括で欲しいテーブルのレコードをテキスト出力したいなぁ、と思いPoweShellスクリプトで組んで使用しています。

仕様

そんなリッチなものではなく、

• 任意の数のsqlファイルを実行して同名でテキスト出力する(csv形式)
• SQL内の任意の数のパラメータを実行時の標準入力から指定可能

という単純なもの。

実行イメージ

実装

全体構成

↑のようなフォルダ構成で使用します。

settings.json

DBの接続情報や設定は、作業の都度変えたいので編集しやすいように外部ファイル化

PowerShellだとjsonは一発で読み込めてむちゃくちゃ使い勝手がいいですね。構造もシンプルで編集しやすいですし。

{
	"server": "SERVERNAME\\INSTANCENAME",
	"database": "DATABASENAME",
	"user": "",
	"password": "",
	"formatstrings": 1
}

○設定項目

output.ps1

function Build-SqlCmd() {
    $cmd = "sqlcmd"

    $cmd += " -S " + (Add-Quote  $settings.server)

    if( [string]::IsNullOrEmpty($settings.user) ) {
        $cmd += " -E"
    } else {
        $cmd += " -U " + (Add-Quote $settings.user)
        $cmd += " -P " + (Add-Quote $settings.password)
    }

    $cmd += " -d " + (Add-Quote $settings.database)
    $cmd += " -i " + (Add-Quote $workPath)

    $cmd += " -s,"
	$cmd += " -y 0"
    $cmd += " -o " + (Add-Quote $outputPath)

    return $cmd
}

function Add-Quote($str) {
    return '"' + $str + '"'
}

Set-Location -Path $PSScriptRoot
$workFolder = ".\work"
$outputFolder = ".\output"
$settings = Get-Content .\settings.json -Raw | ConvertFrom-Json

$formats = New-Object System.Collections.ArrayList
if($settings.formatstrings -ge 1) {
    for ($i=0; $i -lt $settings.formatstrings; $i++) {
        $inp = Read-Host "{$i}"
        [void]$formats.Add($inp)
    }
}

$scripts = Get-ChildItem -Path .\sql -Include *.sql -recurse
foreach( $script in $scripts ) {
    $workPath = Join-Path $workFolder $script.Name
    $outputPath = Join-Path $outputFolder ($script.BaseName + ".csv")
    Copy-Item -Path $script.FullName -Destination $workPath 
    (Get-Content -Encoding default $workPath) | 
    ForEach-Object {
        $line = $_
        for ($i=0; $i -lt $formats.Count; $i++) { 
            $line = $line -replace "\{$i\}", $formats[$i]
        }
        $line
    } | Out-File -Encoding default $workPath
    $sqlCmd = Build-SqlCmd
    CMD /C $sqlCmd
}

やっつけですが、シンプルに以下の処理を行います。

①パラメータを入力させる
　※設定ファイルのformatstringsの数分

②sqlフォルダ内のsqlファイルをすべて取得する
　※直下のみ

＜②で取得したファイル数分ループ＞

　③クエリ内の置換対象文字列を①で置換する
　　※ファイルのコピーをworkフォルダに作成後、中身を置換

　④SQLCMDでSQLを発行し、結果をCSVファイルとして出力
　　※outputフォルダに元sqlファイルと同名で出力

＜/②で取得したファイル数分ループ＞

sqlファイルは文字コードはSJISの想定。

PowerShellでSQL Serverにクエリを発行してテキスト出力する方法は、

• SQLCMD
• Invoke-Sqlcmd
• 自作（.NETのライブラリを使用）

などがあるみたい。

Invole-Sqlcmdは、Install-Moduleが必要なので外部のネットワークに接続できないコンピュータでは使えなそうでした。また自作は時間がかかりそうだったので、SQLCMDを使用しています。

ただ、SQLCMDだとヘッダーの出力に難があり、ヘッダーは出力なしとしています。
何かいい方法ないものか…

おわりに

ちなみに更新前後の比較だけであれば、「A5:SQL Mk-2」というフリーのSQLクライアントが便利です。比較結果をHTML出力やExcel出力できたりするのでエビデンス作成にも便利。

他にも世の中には便利なフリーソフトが溢れているのでしょうけど、会社では基本的にフリーソフトは無条件では使用できず申請が必要になりますよね。そんなときPowerShellやバッチが使えると代替できてとても便利です。勉強せねば・・・

世の中のイケイケなシステム屋さんって、どんな方法でやっているのでしょう。気になる〰。