【ExcelVBA】SQL Serverのデータベースのユーザ定義テーブル名を取得するには

この記事では、SQL Serverのデータベースのユーザ定義テーブル名を取得する方法についてご説明します。

Table of Contents

【動画】SQL Serverのデータベースのユーザ定義テーブル名を取得する実際の動き

本題に入る前に、まずは次のツイートをご覧ください。

SQL Serverのデータベースからユーザ定義テーブル名を取得するマクロを作成してみました😊

ユーザ定義テーブル名を取得するSELECT文を使えば簡単に取得ができます😃#Excel #SQLServer pic.twitter.com/iKmSPWDaCQ

— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) September 4, 2021

マクロがSQL Serverのデータベースのユーザ定義テーブル名を取得しています。
(取得したユーザ定義テーブル名をF列のセルに書き出しています)

目次へ戻る

ユーザ定義テーブル名の取得はSQL Serverのデータベースのユーザ定義テーブル名を取得するSELECT文を実行する

SQL Serverのデータベースのユーザ定義テーブル名を取得するには、SQL Serverのデータベースのユーザ定義テーブル名を取得するSELECT文を実行します。

SQL Serverのデータベースのユーザ定義テーブル名を取得するSELECT文は次の通りです。

SELECT
  name
FROM
  sys.objects
WHERE
  type = 'U'
order by
  name

このSELECT文をマクロが実行すると、SQL Serverのデータベースのユーザ定義テーブル名が取得できます。
※order by句は必須ではありません。(省略可)

ちなみに、SQL Serverに直接ユーザ定義テーブル名を取得するSELECT文を実行した結果は次の通りです。

SELECT文を実行した結果

SQL Serverのデータベース「testDataDB」のユーザ定義テーブル名が取得できています。

目次へ戻る

SQL Serverのデータベースのユーザ定義テーブル名を取得する方法

SQL Serverのデータベースのユーザ定義テーブル名を取得するには、次の流れの通りにコードを書いていきます。

Excelのマクロ

STEP.1

SQL Serverへの接続情報取得

SQL Serverに接続するための接続情報を取得します。

STEP.2

Connectionオブジェクトのインスタンスの生成

Connectionオブジェクトのインスタンスを生成します。
このインスタンスは、マクロがSQL Serverに接続するために必要です。

STEP.3

SQL Serverに接続

STEP.1の接続情報をもとにSQL Serverに接続します。

STEP.4

Recordsetオブジェクトのインスタンスの生成

Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、SQL Serverのテーブルデータの参照・挿入に必要です。

STEP.5

Recordsetオブジェクトに接続先情報を設定

RecordsetオブジェクトのActiveConnectionプロパティに、STEP.1で取得した接続先情報が設定されているConnectionオブジェクトのインスタンスを設定します。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。

STEP.6

SQL Serverのデータベースのユーザ定義テーブル名を取得

SQL Serverのデータベースのユーザ定義テーブル名を取得します。
SQL Serverのデータベースのユーザ定義テーブル名の取得は、SQL Serverのデータベースのユーザ定義テーブル名が取得できるSELECT文を用意し、Getrowsメソッドを実行することで取得することができます。

目次へ戻る

コードの例

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

    Dim DBName      As String               'データベース名
    Dim connDB      As String               'データベース接続情報
    Dim itm         As Variant              '配列から取得した値を格納する変数
    Dim sqlStr      As String               'SQL文
    Dim cnt         As Long                 'カウンタ
    Dim getVal()    As Variant              '取得したデータ格納用並列
    Dim oCon        As ADODB.Connection     'Connection用変数
    Dim oRS         As ADODB.Recordset      'レコードセット用変数
    
    'カウンタを初期化する
    cnt = 2
 
    'データベースの名前
    DBName = "testDataDB"
    
    'DB(SQL Server)接続情報を取得
    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
    
    'ユーザ定義のテーブル名を取得する
    sqlStr = "SELECT name FROM sys.objects WHERE type = 'U' order by name"
 
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
     
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open
    
    'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
    getVal = oRS.GetRows
    
    'レコードセットを閉じる
    oRS.Close
    
    For Each itm In getVal()
        'ユーザ定義テーブル名をF列のセルに設定する
        Range("F" & cnt).Value = itm
        cnt = cnt + 1
    Next
    
    '各終了処理
    oCon.Close
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCon Is Nothing Then Set oCon = Nothing

コードの解説

注目すべきコード①

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

    'ユーザ定義のテーブル名を取得する
    sqlStr = "SELECT name FROM sys.objects WHERE type = 'U' order by name"
 
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
     
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open
    
    'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
    getVal = oRS.GetRows

37行目でSQL Serverのデータベースのユーザ定義テーブル名を取得するSELECT文を用意し、そのSELECT文を40行目のsourceプロパティに設定します。

次に43行目でrecordsetのOpenメソッドを実行してカーソルを開いた後に、46行目のGetRowsメソッドを実行すると、SQL Serverのデータベースのユーザ定義テーブル名を取得することができます。

上記のコードでは取得した列名を配列getValに格納しています。

注目すべきコード②

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

    'データベースの名前
    DBName = "testDataDB"
    
    'DB(SQL Server)接続情報を取得
    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

14行目でユーザ定義テーブル名を取得したい対象のデータベース名を指定しています。

14行目のデータベース「testDataDB」に対する接続情報を17行目から20行目でConnDBに格納し、そのConnDBを26行目のOpenメソッドの引数に渡すことで、データベース「testDataDB」のユーザ定義テーブル名を取得できるようになります。

目次へ戻る

SQL Serverのデータベースのユーザ定義テーブル名を取得した結果

SQL Serverのデータベースのユーザ定義テーブル名を取得した結果は下の画像になります。

F列のセルに、取得したSQL Serverのデータベース「testDataDB」のユーザ定義テーブル名が書き込まれています。

ちなみに、SQL Serverのデータベース「testDataDB」のユーザ定義テーブル名を直接見てみると次の通りです。

目次へ戻る

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

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

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

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

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

    Dim oCon        As ADODB.Connection     'Connection用変数
    Dim oRS         As ADODB.Recordset      'レコードセット用変数

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

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

目次へ戻る

最後に

本記事では、SQL Serverのデータベースのユーザ定義テーブル名を取得する方法についてご説明しました。

SQL Serverのデータベースのユーザ定義テーブル名を取得するSELECT文をマクロが用意し、そのSELECT文を実行すればSQL Serverのデータベースのユーザ定義テーブル名を簡単に取得することができます。

SQL Serverのデータベースのユーザ定義テーブル名を取得したい時に本記事を参考にしていただけたら幸いです。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間無料体験」はこちら

目次へ戻る