【ExcelVBA】SQL ServerのストアドプロシージャのソースコードをExcelのシートに出力する方法とは

11分

SHARE

この記事では、SQL ServerのストアドプロシージャのソースコードをExcelのシートに出力する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

【動画】SQL ServerのストアドプロシージャのソースコードをExcelのシートに出力する実際の動き

本題に入る前に、まずは次の動画をご覧ください。


ソースコードを参照したいストアドプロシージャを、ストアドプロシージャ「sp_helptext」を使って参照しています。

ストアドプロシージャ「sp_helptext」のパラメタにソースコードを参照したいストアドプロシージャを指定してマクロが「sp_helptext」を呼び出しています。

呼び出された「sp_helptext」がマクロ側から渡されたソースコードを参照したいストアドプロシージャを受け取って実行され、実行後に出力されるソースコードをマクロが受け取ってそのソースコードをExcelのシートに出力しています。

今回のマクロのポイントはあくまで、「sp_helptext」を利用している点です。

マクロ単体では実現ができないので、「sp_helptext」を借りて実現させているというのが今回の処理の大まかな内容になります。

マクロ作成の流れ

STEP.1
SQL Serverへの接続情報取得
SQL Serverに接続するための接続情報を取得します。
STEP.2
Connectionオブジェクトのインスタンスの生成
Connectionオブジェクトのインスタンスを生成します。
このインスタンスは、マクロがSQL Serverに接続するために必要です。
STEP.3
SQL Serverに接続
STEP.1の接続情報をもとにSQL Serverに接続します。
STEP.4
ADODB.Commandオブジェクトのインスタンスを生成する
ADODB.Commandオブジェクトのインスタンスを生成します。
このインスタンスは、SQL Serverのストアドプロシージャを実行するのに必要です。
STEP.5
実行するストアドプロシージャのパラメタに必要な情報を設定する
実行するストアドプロシージャのパラメタに必要な情報を設定します。
今回は「sp_helptext」というストアドプロシージャを実行するのですが、そのストアドプロシージャには一つパラメタが必要なので、そのパラメタに設定する必要な各種情報(パラメタの名前、パラメタに渡す値、など)を用意します。
STEP.6
STEP.5のストアドプロシージャを実行する
STEP.5のストアドプロシージャを実行します。
実行するストアドプロシージャは「sp_helptext」です。
「sp_helptext」はストアドプロシージャのソースコードを取得するストアドプロシージャです。
STEP.7
STEP.6で取得したストアドプロシージャのソースコードをセルに出力する
STEP.6で取得したストアドプロシージャのソースコードをセルに出力します。
目次へ戻る

Excelファイルの例

今回用意したExcelファイルは次の通りです。

黄色のA2のセルにソースコードを取得したいストアドプロシージャ名を入力して「参照する」ボタンをクリックすると、セルB9から取得したソースコードが出力されます。

ちなみに、取得したソースコードのストアドプロシージャは以下の画像の通りです。

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Private Sub btn_getSPSC_Click()

    Dim connDB              As String                   'データベース接続情報
    Dim objConnection       As ADODB.Connection         'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim objCommand          As ADODB.Command            'ADODB.Commandオブジェクトのインスタンス用変数
    Dim objParameter        As ADODB.Parameter          'パラメタオブジェクト用変数
    Dim rVal                As Object                   'ストアドプロシージャを実行して取得する値用変数
    Dim cnt                 As Long                     'カウンタ
    
    Const DBName            As String = "testDataDB"    'データベース名
    Const SpName            As String = "sp_helptext"   'ストアドプロシージャ名
    Const pstRowPos         As Long = 9                 'ソースコードを貼り付ける行位置
        
    'カウンタを初期化する
    cnt = pstRowPos
    
    'データベース接続情報を取得
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"
 
    'ADODB.Connectionオブジェクトのインスタンスを生成する
    Set objConnection = New ADODB.Connection
    
    'DBをOpenする
    objConnection.Open connDB
 
    'ADODB.Commandオブジェクトのインスタンスを生成する
    Set objCommand = New ADODB.Command
    
    With objCommand
    
        'Command.ActiveConnectionプロパティに、ADODB.Connectionオブジェクトを設定する
        Set .ActiveConnection = objConnection
        
        'Command オブジェクトのコマンドの種類にストアドプロシージャを指定するため、adCmdStoredProcを設定する
        .CommandType = adCmdStoredProc
        
        'ストアドプロシージャ名を取得する
        .CommandText = SpName
        
        'タイムアウト時間を設定する(ここでは30秒にしています。※ご使用の環境で適切な時間に指定してください)
        .CommandTimeout = 30
        
        'ストアドプロシージャのパラメタの設定を行う
        '第1引数:パラメタの名前を指定
        '第2引数:パラメタのデータの型を指定
        '第3引数:指定するパラメタは入力パラメタなので「adParamInput」を指定
        '第4引数:パラメタのサイズを指定(ひとまず50を指定)
        '第5引数:パラメタに渡す値を指定(ストアドのソースコードを参照したいストアド名を指定)
        .Parameters.Append .CreateParameter("objname", _
                                             adWChar, _
                                             adParamInput, _
                                             50, _
                                             Worksheets("work").Range("spNM").Value)
                
        'ストアドプロシージャ「sp_helptext」を実行し、参照したいストアドプロシージャのソースコードを取得する
        Set rVal = .Execute
        
        '取得したソースコードの行数だけループさせる
        Do Until rVal.EOF
            
            '取得したソースコードをセルに出力する(ソースコードに改行文字が含まれる場合は取り除く)
            Worksheets("work").Range("B" & cnt).Value = Replace(rVal.Fields(0).Value, Chr(10), "")
            
            '次のレコードに移動する
            rVal.MoveNext
            
            cnt = cnt + 1
            
            DoEvents
        
        Loop
    
    End With
    
    '後処理
    objConnection.Close
    If Not objConnection Is Nothing Then Set objConnection = Nothing
    If Not objCommand Is Nothing Then Set objCommand = Nothing
    If Not objParameter Is Nothing Then Set objParameter = Nothing
    
End Sub

注目すべきコード①

最初に見て頂きたいのは12行目と20行目から23行目です。

    Const DBName            As String = "testDataDB"    'データベース名
    'データベース接続情報を取得
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"

以上のコードは、SQL Serverのデータベースに接続するための情報を用意するためのコードです。

12行目で接続先のデータベースを取得し、20行目から23行目でSQL Serverのデータベースに接続するための情報を用意しています。

なお、22行目では12行目で取得したデータベース名を設定しています。

注目すべきコード②

次に見て頂きたいのは26行目から29行目です。

    'ADODB.Connectionオブジェクトのインスタンスを生成する
    Set objConnection = New ADODB.Connection
    
    'DBをOpenする
    objConnection.Open connDB

以上のコードは、ADODB.Connectionオブジェクトのインスタンスを生成し、Openメソッドを実行してSQL Serverのデータベースに接続するコードです。

Openメソッドの引数には「注目すべきコード①」で取得したデータベース接続情報を指定しています。

注目すべきコード③

次に見て頂きたいのは32行目から43行目です。

    'ADODB.Commandオブジェクトのインスタンスを生成する
    Set objCommand = New ADODB.Command
    
    With objCommand
    
        'Command.ActiveConnectionプロパティに、ADODB.Connectionオブジェクトを設定する
        Set .ActiveConnection = objConnection
        
        'Command オブジェクトのコマンドの種類にストアドプロシージャを指定するため、adCmdStoredProcを設定する
        .CommandType = adCmdStoredProc
        
        'ストアドプロシージャ名を取得する
        .CommandText = SpName

以上のコードは、SQL Serverのストアドプロシージャを実行するための設定処理を行うコードです。

32行目でADODB.Commandオブジェクトのインスタンスを生成します。このインスタンスはストアドプロシージャの実行に必要なインスタンスです。

このインスタンスに対して、ストアドプロシージャの実行に必要な各種情報を設定していきます。

37行目では、Connectionインスタンス(開いている接続)とADODB.Commandオブジェクトのインスタンスを関連付けています。

40行目では、Commandオブジェクトのコマンドの種類にストアドプロシージャを指定するため、定数adCmdStoredProcを設定しています。

43行目では、マクロが実行したいストアドプロシージャの名前を指定しています。

今回はストアドプロシージャのソースコードを取得してくれるストアドプロシージャ名「sp_helptext」が格納された変数SpNameをCommandTextプロパティに設定しています。

ちなみに変数SpNameにストアドプロシージャ名「sp_helptext」を格納する処理は13行目で行っています。

    Const SpName            As String = "sp_helptext"   'ストアドプロシージャ名

注目すべきコード④

次に見て頂きたいのは54行目から58行目です。

        'ストアドプロシージャのパラメタの設定を行う
        '第1引数:パラメタの名前を指定
        '第2引数:パラメタのデータの型を指定
        '第3引数:指定するパラメタは入力パラメタなので「adParamInput」を指定
        '第4引数:パラメタのサイズを指定(ひとまず50を指定)
        '第5引数:パラメタに渡す値を指定(ストアドのソースコードを参照したいストアド名を指定)
        .Parameters.Append .CreateParameter("objname", _
                                             adWChar, _
                                             adParamInput, _
                                             50, _
                                             Worksheets("work").Range("spNM").Value)

以上のコードは、ストアドプロシージャのソースコードを取得してくれるストアドプロシージャ「sp_helptext」のパラメタに関する必要な情報を設定する処理のコードです。

ストアドプロシージャ「sp_helptext」は必須のパラメタが一つあり、そのパラメタに必要な情報をマクロ側が用意する必要がります。

実際にストアドプロシージャ「sp_helptext」のソースコードを見てみると、objnameというパラメタが使われています。

このobjnameというパラメタに必要な各情報をマクロ側で用意しないままストアドプロシージャ「sp_helptext」を実行しようとしてもエラーとなるので必ずパラメタに関する必要な情報の設定を行います。

【第1引数】パラメタの名前を指定

第1引数には、パラメタの名前を指定します。

ストアドプロシージャ「sp_helptext」のパラメタの名前は「objname」なので、この「objname」の文字列を第1引数に指定します。

【第2引数】パラメタのデータの型を指定

第2引数にはパラメタのデータの型を指定します。

文字列型を指定するために「adWChar」を指定しています。

【第3引数】指定するパラメタは入力パラメタなので「adParamInput」を指定

第3引数にはパラメタが入力なのか出力なのか、またはストアドプロシージャからの戻り値なのか、これらのいずれかを指定します。

ここでは入力パラメタなので、入力パラメタを示す定数「adParamInput」を第3引数に指定します。

【第4引数】パラメタのサイズを指定

第4引数にはパラメタのサイズを指定を指定します。

パラメタに渡したい値の文字サイズに合わせて値を指定します。(今回はひとまず50を指定)

【第5引数】パラメタに渡す値を指定

第5引数にはパラメタに渡す値を指定します。

今回はストアドのソースコードを参照したいストアド名を第5引数に指定します。

(今回はWorksheets(“work”).Range(“spNM”).Valueからストアドプロシージャの名称を取得して第5引数に指定しています。)

注目すべきコード⑤

次に見て頂きたいのは61行目から78行目です。

        'ストアドプロシージャ「sp_helptext」を実行し、参照したいストアドプロシージャのソースコードを取得する
        Set rVal = .Execute
        
        '取得したソースコードの行数だけループさせる
        Do Until rVal.EOF
            
            '取得したソースコードをセルに出力する(ソースコードに改行文字が含まれる場合は取り除く)
            Worksheets("work").Range("B" & cnt).Value = Replace(rVal.Fields(0).Value, Chr(10), "")
            
            '次のレコードに移動する
            rVal.MoveNext
            
            cnt = cnt + 1
            
            DoEvents
        
        Loop
    
    End With

以上のコードは、ExcelのマクロがSQL Serverにストアドプロシージャ「sp_helptext」を実行するよう呼び出し、参照したいストアドプロシージャのソースコードを取得後にセルに出力するコードです。

61行目でExcelのマクロがSQL Serverにストアドプロシージャ「sp_helptext」を実行するよう呼び出します。

この処理により「sp_helptext」が実行され、ストアドプロシージャのソースコードを変数rValが受け取ります。

67行目で、この変数rValから参照したいストアドプロシージャのソースコードを取り出してExcelのシートのセルに出力します。

なお、この67行目のコードを1回実行しただけではストアドプロシージャのソースコードが1行しか取れません。

1行取り出したら、次の行のソースコードを取り出すのに70行目のMoveNextメソッドを実行させます。

MoveNextメソッドを実行させることでソースコードの次の1行を参照することができるので、この「ソースコードを取得→次の行に移る→ソースコードを取得→次の行に移る→…」の処理を繰り返します。

この繰り返しの処理は、64行目のDoループのコードで「EOF(最終行)まで繰り返す」と記述がある通りソースコードの最終行まで繰り返し、最終行まで達したらその最終行をExcelのシートの書き出して処理のループを終えます。

目次へ戻る

動作確認

今回は以下のストアドプロシージャのソースコードを取得してExcelのシートに出力させます。

マクロ実行前

参照したいストアドプロシージャの名称をセルA2に入力して「参照する」ボタンをクリックします。

マクロ実行後

ストアドプロシージャのソースコードがExcelのシートに出力されました。

なお、出力先のセル位置はA9のセルからです。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

  1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelのマクロのコードの6行目の「ADODB.Connection」と7行目の「ADODB.Command」、8行目の「ADODB.Parameter」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim objConnection       As ADODB.Connection         'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim objCommand          As ADODB.Command            'ADODB.Commandオブジェクトのインスタンス用変数
    Dim objParameter        As ADODB.Parameter          'パラメタオブジェクト用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Command」「ADODB.Parameter」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

本記事では、SQL ServerのストアドプロシージャのソースコードをExcelのシートに出力する方法についてご説明しました。

作成したSQL Serverのストアドプロシージャのソースコードは、SQL Serverにストアドプロシージャ「sp_helptext」にそのソースを参照したいストアドプロシージャ名を引数にして呼び出すことで実現可能です。

もし作成したSQL ServerのストアドプロシージャのソースコードをExcel側で参照したい場合は参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→　受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら



目次へ戻る

前の記事

【ExcelVBA】作成したSQL Serverのストアドプロ…

次の記事

【ExcelVBA】指定した行数からCSVファイルを読み込んで…