概要
- DB情報に基づいて自動的にダミーのSQLインサート文を生成するスクリプトです。
- 単体テストなどで、とりあえずDBに登録出来るインサート文を作るのが主要な目的です。
- テーブルを操作するアプリをテストする場合、テストデータとして事前にレコードの登録が必要になる場合があります。
- 業務システムのテーブルはカラム数が数十になることもあり、カラムの毎にnull許容・非許容や型に応じて値を指定するのは大変です。
- このスクリプトでは、「とりあえず」でテーブルに登録可能なインサート文をファイルに出力します。このファイルをテスト仕様に合わせて変更して使う想定です。
- 動作確認で使用した環境は次の通りです。
OS Windows 10(64ビット)
PowerShell(5.1.19041.1682), .NET Framework v4(4.8.04084)DB Microsoft SQL Server Developer 64ビット版(15.0.2095.3)
スクリプトの説明
- 接続先DBに格納される各テーブルにインサート可能なSQLインサート文をファイルに出力します。
（動作確認しやすいようtruncate文もコメントアウトして出力します。） - 実行結果のサンプルを次に示します。（横に長いので一部は”…”で省略しています。）1234567-- truncate table [m_dbtypes];-- truncate table [m_employee];insert into [m_dbtypes] ([n1], ..., [d1], ..., [u2], ..., [o4]) VALUES(1, ..., '2001-01-01', ..., N'v', ..., newid());insert into [m_employee] ([name], ..., [updated_by], [updated_on], ...) VALUES(N'v', ..., 'system', getdate(), ...);
- 対象テーブルは既定で全てのテーブルですが、スキップ対象テーブルを指定する事もできます。
- インサート文は各テーブル（昇順）で１行づつ出力します。
列の値は次の条件で決定していますが、要件に応じて適宜変更してください。列の条件 出力値（列に対応する値） Identity指定あり 自動採番するため列名・値は出力しない。 特定の列名 列名に応じて固定値を出力する。
（業務システムの監査用列に特定値を設定するために用意した。）null許容 nullを出力する。 null非許容 型に応じて登録可能な値を決定する。（SQL Server 2019の型を想定）
値から型（と対応カラム）を推測できるよう、各型でユニークな値を使用している。 - 出力値を決定できない場合、「」を値として出力します。（この値だとSQL実行時にエラーになります。）
- 参考ですが、このスクリプトは次の設計方針に基づいています。
- Window環境での使用を前提とします。
- （開発環境等のツールの準備を必要とせず）簡単に実行できるようにするためにPowerShellで実現します。
- SQL Server 2019を使用する想定ですが、PostgreSQL等の別のDBでも使用できるよう、古くからある安定したODBC系APIを使用します。
- 同様に特定DBに依存しないよう、テーブル・カラム情報の取得にはISO標準のINFORMATION_SCHEMAスキーマを使用します。
使用方法
使用可能なDBが用意されている前提です。
（このスクリプトはSQL Server 2019でしか確認できていません。）
- スクリプトのダウンロード
- スクリプトCreateSqlInserts.ps1をこちら(github)から取得します。
- 必要であれば、サンプルのテーブル定義(DDL)を使用することもできます。
- ODBCデータソースの追加
- Windows管理ツールの「ODBCデータソース」(odbcad32.exe)を開き、接続先DBを登録します。
- 試すだけであれば、「ユーザーDSN」タブで追加ボタンを押し、ウィザードに沿って情報を入力していけば登録が完了します。
- SQL Serverをデータソースとして登録する手順をこちらで紹介しています。
- スクリプトの接続文字列を修正
- スクリプトの冒頭にある$connStr変数の値のDSN=の値を変更します。
- 次の例では、ODBCデータソースで登録した”sqlserver_local”というデータソースを指定しています。123# 接続文字列# - "ODBCデータソース"を開き、データソース(DSN)を追加している前提$connStr = "DSN=sqlserver_local;"
- スクリプトを実行
- スタートメニューからPowerShellを起動し、CreateSqlInserts.ps1を実行します。
（後述の実行権限があればスクリプトを右クリックして「PowerShellで実行」でも良いのですが、実行状況を把握しづらいのでこの手順にしています。） - 実行時に「…CreateSqlInserts.ps1 はデジタル署名されていません。このスクリプトは現在のシステムでは実行できません。」(PSSecurityException/UnauthorizedAccess)エラーが発生する場合、次のようにSet-ExecutionPolicyコマンドレットで許可を与えます。（RemoteSigned：「リモートのスクリプトは署名が必要だがローカルのスクリプトは署名不要」、Process：「このPowerShell上でのみ有効」の意）12345> <span class="ndw-strong-red">Set-ExecutionPolicy RemoteSigned -Scope Process</span>実行ポリシーの変更実行ポリシーを変更すると、...で説明されているセキュリティ上の危険にさらされる可能性があります。実行ポリシーを変更しますか?[Y] はい(Y) [A] すべて続行(A) [N] いいえ(N) [L] すべて無視(L) [S] 中断(S) [?] ヘルプ (既定値は "N"): <span class="ndw-strong-red">y</span>
- スタートメニューからPowerShellを起動し、CreateSqlInserts.ps1を実行します。
ソースコード
全体を把握しやすいよう体裁を整えています。完全なコードはこちらをご覧ください。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | # 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), " + "COLUMN_NAME, 'IsIdentity') as _IS_IDENTITY " + <code> "from INFORMATION_SCHEMA.COLUMNS " + "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 = "" } $cols += "[$colName]"; $vals += $val } $colListReader.Dispose(); # INSERT文を組み立ててファイルに出力 $colsStr = $cols -join ", "; $valsStr = $vals -join ", " $insert = "insert into [$table] ($colsStr) VALUES <code>r</code>n" + ` " ($valsStr);" Write-Result($insert) } $conn.Dispose() |
参考
ODBCの設定例
- スタートメニューから「ODBCデータソース」を開くか、odbcad32.exeを起動します。
- データソース種類のタグを選択し、「追加」をクリックします。
（全てのユーザで共有したい場合は「システムDSN」を選択しますが管理者権限が必要になります。試したいだけなら管理者権限が不要な「ユーザーDSN」を選択します。）
- ここでは「SQL Server」を選択しています。
- データソース名とサーバ名を入力します。
（SQL Serverのホスト名でありlocalhostでは接続不可です。ローカルにSQL Serverをインストールしている場合、既定でそのコンピュータ名になります。）
- 接続先DBで許可している認証方法に応じて設定を変更します。
- 既定のデータベースを指定します。
ここではTestというデータベースを指定しています。
- 既定値で問題ありませんが、希望があれば変更します。
- 後で接続できないと面倒なので、ここでテストしておきます。
- 登録に成功すると、次のように一覧に表示されます。
