【ExcelVBA】作成したSQL Serverのストアドプロシージャの名称を全て取得する方法とは

この記事では、作成したSQL Serverのストアドプロシージャの名称を全て取得する方法についてご説明します。

【動画】作成したSQL Serverのストアドプロシージャの名称を全て取得する実際の動き

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

ExcelのマクロがSQL Serverに接続し、データベースに作成されたストアドプロシージャの名称を取得するSELECT文を実行しています。

実行するとストアドプロシージャの名称が取得されるので、取得したストアドプロシージャの名称をExcelのシートに出力しています。

マクロ作成の流れ

コードの例

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

Option Explicit

Sub getSPNM()

    Dim DBName      As String               'データベースの名前用変数
    Dim connDB      As String               'データベース接続情報用変数
    Dim oCon        As ADODB.Connection     'Connectionオブジェクトのインスタンス用変数
    Dim oRS         As ADODB.Recordset      'recordset用変数
    Dim sqlStr      As String               'SQL文用変数

    'データベースの名前
    DBName = "projDB"
    
    'データベース接続情報を取得
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"
    
    'Connectionオブジェクトのインスタンスを生成する
    Set oCon = New ADODB.Connection
    
    'SQL Serverに接続する
    oCon.Open connDB

    'Recordsetオブジェクトのインスタンスを生成する
    Set oRS = New ADODB.Recordset

    '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、
    'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
    '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
    oRS.ActiveConnection = oCon
    
    'ストアドプロシージャの名称を取得するSQL文を組み立てる
    sqlStr = "SELECT sysobjects.name"
    sqlStr = sqlStr & " FROM sysobjects"
    sqlStr = sqlStr & " WHERE sysobjects.type ='P'"
    
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
    
    'ストアドプロシージャの名称を取得するSQL文を実行する
    Set oRS = oCon.Execute(sqlStr)
    
    'レコード有無判定(EOFプロパティがTrueならデータなし、Falseならデータあり)
    If oRS.EOF = True Then

        'データが存在ない場合

    Else

        'データが存在する場合
        
        '取得したストアドプロシージャ名をセルに出力する
        Worksheets("work").Cells(1, 1).CopyFromRecordset oRS

    End If
    
    '各終了処理
    oRS.Close
    oCon.Close
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCon Is Nothing Then Set oCon = Nothing
    
End Sub

注目すべきコード①

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

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

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

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

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

注目すべきコード②

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

    'Connectionオブジェクトのインスタンスを生成する
    Set oCon = New ADODB.Connection
    
    'SQL Serverに接続する
    oCon.Open connDB

以上のコードは、ExcelのマクロがSQL Serverのデータベースに接続する処理のコードです。

注目すべきコード③

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

    'Recordsetオブジェクトのインスタンスを生成する
    Set oRS = New ADODB.Recordset

    '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、
    'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
    '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
    oRS.ActiveConnection = oCon

27行目ではrecordset用のインスタンスを生成しています。

32行目では、先ほど取得したデータベース接続情報が設定されているobjConnectionをActiveConnectionプロパティに設定し、ConnectionオブジェクトとRecordsetオブジェクトを関連付けます。

以上のコードを実行することで、DBに作成されたストアドプロシージャの名称を取得するSELECT文(※このあとの「注目すべきコード④」で説明します)を実行してストアドプロシージャの名称を取得することができます。

注目すべきコード④

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

    'ストアドプロシージャの名称を取得するSQL文を組み立てる
    sqlStr = "SELECT sysobjects.name"
    sqlStr = sqlStr & " FROM sysobjects"
    sqlStr = sqlStr & " WHERE sysobjects.type ='P'"
    
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
    
    'ストアドプロシージャの名称を取得するSQL文を実行する
    Set oRS = oCon.Execute(sqlStr)

以上のコードは、データベースに作成されたストアドプロシージャの名称を取得するSELECT文を実行し、取得するコードです。

35行目でSELECT文を用意し、40行目でそのSELECT文をSourceプロパティに設定後、43行目でそのSELECT文を実行します。

取得したストアドプロシージャの名称は、変数oRSに格納されます。

注目すべきコード⑤

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

    'レコード有無判定(EOFプロパティがTrueならデータなし、Falseならデータあり)
    If oRS.EOF = True Then

        'データが存在ない場合

    Else

        'データが存在する場合
        
        '取得したストアドプロシージャ名をセルに出力する
        Worksheets("work").Cells(1, 1).CopyFromRecordset oRS

    End If

以上のコードは、変数oRSから取得したストアドプロシージャ名をセルに出力するコードです。

46行目で変数oRSにデータが存在しているのか確認し、存在している場合は55行目でCopyFromRecordsetメソッドを使って変数oRSからストアドプロシージャの名称をセルに出力しています。

動作確認

SQL Serverにデータベース「projDB」があり、そのデータベースには以下の4つのストアドプロシージャが存在しています。

以上の4つのストアドプロシージャの名前をExcelのシートに出力します。

マクロ実行前

以下のシートには何も入力されていません。

マクロ実行後

マクロを実行すると、4つのストアドプロシージャの名前がシートに出力されました。

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

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

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

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

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

    Dim oCon        As ADODB.Connection     'Connectionオブジェクトのインスタンス用変数
    Dim oRS         As ADODB.Recordset      'recordset用変数

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

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

最後に

本記事では、作成したSQL Serverのストアドプロシージャの名称を全て取得する方法についてご説明しました。

作成したSQL Serverのストアドプロシージャの名称は、取得するためのSELECT文をマクロ側で用意し、そのSELECT文をSQL Server側で実行させることで全て取得することができます。

作成したSQL Serverのストアドプロシージャの名称を全て取得したい場合は本記事の内容を参考にしてみてくださいね。

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

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

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

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