SQL Server のビュー／関数定義をバージョン管理する

SQL Server を使ってでデータベースシステムを構築しているならば、様々なビューや関数を作成していると思います。

これらの定義は SQL Server 上に格納されているので、Git などのバージョン管理ツールで直接内容を取得してバージョン管理することができません。

しかし、他のプログラムのソースコード同様にビューや関数の定義は改良が加えられていくので、やはり バージョン管理は必須 になります。

この記事では VBScript を利用 して SQL Server 上のビューや関数の定義をバージョン管理する方法 を解説します。

この方法を活用して SQL Server のデータベースシステムの構築を 安全、確実なもの にしていきましょう！

では、さっそく行ってみましょう！

ビュー／関数定義を取得する SQLクエリ
1. ビュー定義を取得する SQLクエリ
2. 関数定義を取得する SQLクエリ
最も簡単に手に入る SQLクエリ実行環境＝ VBScript
1. SQL Server に接続してビュー／関数定義を出力するコードを書く
タスクスケジューラでビュー／関数定義出力を自動実行する
1. オマケ： BATファイルから VBScript ファイルを実行
まとめ

ビュー／関数定義を取得する SQLクエリ

SQL Server Management Studio（SSMS）を利用すればビューや関数の定義は確認することは可能です。

これを SSMS を利用せずに SQLクエリで取得するところから始めたいと思います。

ビューや関数はシステムテーブルに保管されていますので、ここから取得します。

ビュー定義を取得する SQLクエリ

ビューの名前とビュー定義 を取得するSQLクエリは次のようになります。

SELECT sys.views.name,
    sys.sql_modules.definition
FROM sys.views
    INNER JOIN sys.objects ON sys.objects.object_id = sys.views.object_id
    INNER JOIN sys.sql_modules ON sys.sql_modules.object_id = sys.objects.object_id

関数定義を取得する SQLクエリ

関数の名前と関数定義 を取得するSQLクエリは次のようになります。

SELECT sys.objects.name,
    sys.sql_modules.definition
FROM sys.objects
    LEFT JOIN sys.sql_modules ON sys.objects.object_id = sys.sql_modules.object_id
WHERE sys.objects.type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TF')

最も簡単に手に入る SQLクエリ実行環境＝ VBScript

SQLクエリを実行するプログラムを作成したいなら、勿論流行りの Python などの高級スクリプト言語でも可能です。

しかし、全ての Windows PC に Python がインストールされている訳では無いので、必ず環境構築の手間が発生してしまいます。

ところが、Windows PC には VBScript は標準で搭載されています。

SQL Server に接続してビュー／関数定義を出力するコードを書く

先ほど紹介した SQLクエリを実行 すればビューと関数の定義は取得できます。

これを実行して、出力をテキストファイルに書き出す 部分までを VBScript で記述すれば良い訳です。

非常に短いコードで実行できますので、いきなりコードを紹介します。次のようになります。

' OutputDefinition.vbs

' グローバル変数の定義
Dim cn
Dim rs

' データベース接続を開くサブルーチン
Sub cnOpen()
    Set cn = CreateObject("ADODB.Connection")

    ' *************** データベースの情報を入力する　*********************
    ServerName = "LOCALHOST\sqlexpress"
    dbname = "DBNAME"
    ' *******************************************************************

    cn.Open "Provider=SQLOLEDB;" & _
            "Data Source=" & ServerName & ";" & _
            "Initial Catalog=" & dbname & ";" & _
            "Integrated Security=SSPI "
End Sub

' 定義を出力するサブルーチン
Sub Output_Definition(sqlcom)
    Dim fso, SaveDir, rs, Filename, file

    Set fso = CreateObject("Scripting.FileSystemObject")

    ' *************** 定義ファイルを出力するフォルダパス ****************
    SaveDir = "C:\temp\ViewDefinitions\"
    ' *******************************************************************

    If Not fso.FolderExists(SaveDir) Then
        fso.CreateFolder(SaveDir)
    End If

    Call cnOpen()

    Set rs = cn.Execute(sqlcom)

    Do While Not rs.EOF
        Filename = SaveDir & rs.Fields(0).Value & ".sql"
        Set file = fso.CreateTextFile(Filename, True)
        file.Write rs.Fields(1).Value
        file.Close
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

' メインサブルーチン
Sub main()
    Dim sqlcom

    sqlcom = "SELECT sys.views.name, sys.sql_modules.definition FROM sys.views " & _
             "INNER JOIN sys.objects " & _
             "ON sys.objects.object_id = sys.views.object_id " & _
             "INNER JOIN sys.sql_modules " & _
             "ON sys.sql_modules.object_id = sys.objects.object_id "

    Call Output_Definition(sqlcom)

    sqlcom = "SELECT sys.objects.name, " & _
             "sys.sql_modules.definition " & _
             "FROM sys.objects " & _
             "LEFT JOIN sys.sql_modules ON sys.objects.object_id = sys.sql_modules.object_id " & _
             "WHERE sys.objects.type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TF')"

    Call Output_Definition(sqlcom)
End Sub

' メインサブルーチンの実行
Call main()

このコードをメモ帳などに張り付けて OutputDefinition.vbs など適当な名前をつけて保存して下さい。

コメントを入れている箇所に データベースの情報 と、テキストファイルを出力するフォルダパス が記載されていますので、ご自分の環境に合わせて適宜編集して下さい。

テキストファイルを出力するフォルダパス は Git でバージョン管理している リポジトリのフォルダを指定 することでバージョン管理ができるようになります。

このファイルをダブルクリックして実行すると、指定のフォルダにビューと関数定義の *.sql というファイルが作成されます。

念のためこのコードのファイルを以下のリンク先に保存しましたので、適宜ダウンロードしてご活用下さい。

OutputDefinition ダウンロード

タスクスケジューラでビュー／関数定義出力を自動実行する

プログラマーは何でもコンピュータに仕事をさせたくなるものです。

この VBScript のプログラムを定期的に実行させたいと思います。図のように毎日1時間おきにスクリプトファイルを自動で実行するよう設定します。

オマケ： BATファイルから VBScript ファイルを実行

既に様々な自動実行ジョブを BATファイルで作成されている場合は、以下のコードを BATファイルに追記することで実行することが可能です。

cscript "C:\temp\OutputDefinition.vbs"

まとめ

SQL Server でデータベースシステムを構築していくと徐々にビューや関数が増えてきて Git などのバージョン管理ツールで管理する必要が出てきます。

この記事では身近にあるプログラムの実行環境である VBScript を利用してビューや関数定義を自動でテキストファイルに出力する方法をご紹介しました。

これをリポジトリフォルダに設定することで Git などのバージョン管理ツールでバージョン管理が可能です。

更に、タスクスケジューラで定期的にビューや関数定義を出力することでほぼ全自動の管理が可能になります。

データベースシステムの構築をされている方は是非活用してみて下さい。

ビュー／関数 定義を取得する SQLクエリ