Power Queryで自動生成！Excel定義表からCREATE TABLE文を出力するM言語スクリプト

Excelで管理されたテーブル定義書からSQLのCREATE TABLE文を作成する作業がまぁまぁあります。手作業でSQLを整形するのは時間がかかり、ヒューマンエラーの温床にもなりがちなので、従来はExcel VBAでCREATE TABLE文を生成していました。

PowerQuery(M言語)が思いのほか使えるので、Excelの定義表から自動的にCREATE TABLE文を生成するスクリプトをご紹介します。型の正規化や主キー制約の付与までを一気通貫で処理できるため、効率的かつ堅牢なテーブル作成が可能になります。

CREATE TABLE文生成PowerQuery

次のようなテーブルを「T_テーブル定義」として作ります。

あとは次のクエリを実行すればOK。

let 
    // テーブル名に"APP."のような接頭辞をつける場合に指定
    SchemaPrefix = "",
    Source = Excel.CurrentWorkbook(){[Name="T_テーブル定義"]}[Content],
    /* 関数群の定義 */
    // トリムして文字列に変換
    ToTextTrim = (x as any) as nullable text => if x = null then null else Text.Trim(Text.From(x)),
    // 数値に変換
    ToInt = (x as any) as nullable number => try Number.FromText(Text.From(x)) otherwise null,
    // 型名の正規化(大文字化、空白トリム、同義語)
    NormalizeType = (t as any) as nullable text =>
    let 
        raw = Text.Upper(ToTextTrim(t)?? ""),
        mapped = if raw = "VARCHAR" then "VARCHAR2"
            else if raw = "NVARCHAR" then "NVARCHAR2"
            else raw
    in 
        if mapped = "" then null else mapped,
    // 文字列型？
    IsTextType = (T as nullable text) as logical =>
    let 
        u = Text.Upper(T ?? "")
    in 
        List.Contains({"CHAR", "NCHAR", "VARCHAR", "VARCHAR2", "NVARCHAR2"}, u),
    // 数値型？
    IsNumericType = (T as nullable text) as logical =>
    let 
        u = Text.Upper(T ?? "")
    in 
        List.Contains({"NUMBER", "DECIMAL", "NUMERIC"}, u),
    // 型文字列の整形:文字列は(桁数)、数値型は(precision[, scale])を付与
    FormatType = (baseType as nullable text, precision as nullable number, scale as nullable number) as text =>
    let 
        T=Text.Upper(baseType ?? ""),
        result =
            if IsTextType(T) then 
                if precision <> null then T & "(" & Text.From(precision) & ")" else T
            else if IsNumericType(T) then 
                if precision <> null and scale <> null then T & "(" & Text.From(precision) & ", " & Text.From(scale) & ")"
                else if precision <> null then T & "(" & Text.From(precision) & ")"
                else T
            else 
                if T="" then "TEXT" else T
    in 
        result,
    // PK名:テーブル名に"_"があればその後ろの文字を使い、無ければ全体をPK名にする
    MakePkName = (tableName as text) as text => "CONSTRAINT " & ("PK_" & (if Text.Contains(tableName, "_") then Text.AfterDelimiter(tableName, "_") else tableName)) & " PRIMARY KEY ",
    // 前処理:型の正規化・数値化・行順番保持
    Cleaned = 
    let 
        step1 = Table.TransformColumns(Source, {
            {"テーブル名", each ToTextTrim(_), type text},
            {"項目名", each ToTextTrim(_), type text},
            {"タイプ", each NormalizeType(_), type text},
            {"桁数", each ToInt(_), Int64.Type},
            {"精度", each ToInt(_), Int64.Type},
            {"主キー区分", each ToInt(_), Int64.Type}
        }),
        withIndex = Table.AddIndexColumn(step1, "RowID", 0, 1, Int64.Type)
    in 
        withIndex,
    // テーブル毎にグループ化(順序を維持するためにRowIDでソート)
    Grouped = Table.Group(Cleaned, {"テーブル名"},{{"Def", each Table.Sort(_, {{"RowID", Order.Ascending}}),
        type table [テーブル名 = nullable text, 項目名 = nullable text, タイプ = nullable text, 桁数 = nullable number, 精度 = nullable number, 主キー区分 = nullable number, RowID = number]}}),
    // CREATE TABLE文の各項目名の行を生成してList化
    WithColumnLines = Table.AddColumn(Grouped, "ColumnLines", each 
        let 
            t = [Def],
            recs = Table.ToRecords(t),
            lines = List.Transform(recs, (r as record ) => "    " & r[項目名] & " " & FormatType(r[タイプ], r[桁数], r[精度]))
        in
            lines,
        type list),
    // 主キーの項目名をList化
    WithPkCols = Table.AddColumn(WithColumnLines, "PKCols", each 
        let 
            t = [Def],
            pkRows = Table.SelectRows(t, each [主キー区分] <> null),
            pkSorted = Table.Sort(pkRows, {{"主キー区分", Order.Ascending}}),
            cols = if Table.RowCount(pkSorted) = 0 then {} else pkSorted[項目名]
        in
            cols,
        type list),
    // 主キー制約名を生成(PK_テーブル名のような書式)
    WithPkName = Table.AddColumn(WithPkCols, "PkName", each MakePkName([テーブル名]), type text),
    // SQL文に合成
    WithSQL = Table.AddColumn(WithPkName, "SQL", each 
        let 
            tblRaw = [テーブル名],
            tbl = if SchemaPrefix = "" then tblRaw else SchemaPrefix & tblRaw,
            lines = [ColumnLines],
            pkList = [PKCols],
            bodyLines = if List.IsEmpty(pkList) then 
                    lines
                else
                    List.Combine({
                        lines, {"    " & [PkName] & "(" & Text.Combine(pkList, ",") & ")"}
                    }),
            sql = "CREATE TABLE " & tbl & "(" & "#(lf)" &
                Text.Combine(bodyLines, "," & "#(lf)") & "#(lf)" &
                ");"
        in 
            sql,
        type text),
    // SQLをリストに分解して出力
    ExpandedSQL =
        Table.ExpandListColumn(
            Table.AddColumn(WithSQL, "SQLLines", each Text.Split([SQL], "#(lf)")),
            "SQLLines"
        ),
    AddLineNo = Table.AddIndexColumn(ExpandedSQL, "LineNo", 1, 1, Int64.Type),
    Result = Table.SelectColumns(AddLineNo, {"テーブル名", "SQLLines", "LineNo"})
in
    Result

こんな結果が返ってきます。