テスト用のSQL（INSERT文）を自動生成する方法

# SQL文の出力先ファイル名
$filename = "dummy_insert.sql"

# 接続文字列
$connStr = "DSN=sqlserver_local;"

# 処理対象外のテーブル名
$skipTables = @(
    "m_dbtypes_other", "skip_table1"
)
# カラム名に基づいて決定する値
$valueByColumn = @{
    "created_by" = "'system'"; "created_on" = "getdate()"
    "updated_by" = "'system'"; "updated_on" = "getdate()"
}
# カラム型に基づいて決定する値
$valueByType = @{
    "int" = "1"; "bit" = "2"; "tinyint" = "3"; "smallint" = "4"; "bigint" = "5"
    "date" = "'2001-01-01'"; "datetime" = "'2002-02-02'"
    "nchar" = "N'c'"; "nvarchar" = "N'v'"; "ntext" = "N't'"
    "uniqueidentifier" = "newid()"
}

# ファイル出力関数の宣言
function Write-Result($msg)
{
    Write-Output $msg | Out-File $fullpath -Append -Encoding utf8 # UTF8(BOM)
}

# DB接続
$conn = New-Object System.Data.Odbc.OdbcConnection($connStr)
$conn.open()

# テーブル一覧の取得
$tableListSql = 
    "select TABLE_NAME from INFORMATION_SCHEMA.TABLES order by TABLE_NAME"
$tableListCmd = New-Object System.Data.Odbc.OdbcCommand
$tableListCmd.Connection = $conn
$tableListCmd.CommandText = $tableListSql
$tableListReader = $tableListCmd.ExecuteReader()
$tableList = @()
while($tableListReader.Read()){
    $tableName = $tableListReader["TABLE_NAME"].ToString()
    if( ! $skipTables.Contains($tableName) ){
        $tableList += $tableName
    }
}
$tableListReader.Dispose()

# 出力先ファイルの初期化(空で上書き)
New-Item $filename -Force | Out-Null
$fullpath = Resolve-Path $filename

# truncate文の出力
$tableList | foreach { Write-Result("-- truncate table [$_];") }

# テーブル毎にカラム一覧を処理
Write-Result("")
foreach($table in $tableList) {

    # カラム一覧の取得
    $colListSql =
        "select *, " + <code>
            "COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), " + </code>
                "COLUMN_NAME, 'IsIdentity') as _IS_IDENTITY " + <code>
        "from INFORMATION_SCHEMA.COLUMNS " + </code>
        "where TABLE_NAME = '$table' " + <code>
        "order by ORDINAL_POSITION"
    $colListCmd = New-Object System.Data.Odbc.OdbcCommand
    $colListCmd.Connection = $conn
    $colListCmd.CommandText = $colListSql
    $colListReader = $colListCmd.ExecuteReader()

    # カラム情報に基づいてダミー値を設定
    $cols = @(); $vals = @()
    while($colListReader.Read()) {
        $colName = $colListReader["COLUMN_NAME"].ToString()
        $colType = $colListReader["DATA_TYPE"].ToString()
        $isNullable = $colListReader["IS_NULLABLE"].ToString() -eq "YES"
        $isIdentity = $colListReader["_IS_IDENTITY"].ToString() -eq "1"
        $val = $null

        # 業務要件に合わせて出力値を編集してください！！！！！
        if( $isIdentity ){
            # Identity属性がある列は自動採番ためスキップ
            Write-Host "skip identity column: $table.$colName"
            continue;
        } elseif( $valueByColumn.Contains($colName) ){
            # 特定カラムは固定値を出力
            $val = $valueByColumn[$colName];
        } elseif( $isNullable ){
            # null許容型カラムの場合はnullを出力
            $val = "null"
        } else {
            # null非許容型カラムの場合は型に応じたダミー値を出力
            $val = $valueByType[$colType]
        }

        # 値が定まらなかった場合は警告出力
        if( $null -eq $val -or $val -eq "" ){
            Write-Warning "unknown type: $table.$colName($colType)"
            $val = "/* UNKNOWN: $colName($colType) */"
        }

        $cols += "[$colName]"; $vals += $val
    }
    $colListReader.Dispose();

    # INSERT文を組み立ててファイルに出力
    $colsStr = $cols -join ", "; $valsStr = $vals -join ", "
    $insert = </code>
        "insert into [$table] ($colsStr) VALUES <code>r</code>n" + `
        "    ($valsStr);"
    Write-Result($insert)
}
$conn.Dispose()