この記事では、SQL Serverのテーブルの列名を取得する方法についてご説明します。
【動画】SQL Serverのテーブルの列名を取得する実際の動き
本題に入る前に、まずは次のツイートをご覧ください。
SQL Serverのテーブルの列名を取得するマクロを作ってみました😊
列名が取得できるSELECT文を使えば簡単に取得することができます😃#ExcelVBA#SQLServer pic.twitter.com/mtm3RVec8b
— まさ@Excel、VBA、マクロ(経験年数16年) (@masamasa9785) August 31, 2021
SQL Serverのテーブルの列名をマクロが取得しています。
SQL Serverのテーブルの列名を取得するには、SQL Serverのテーブルの列名を取得するSELECT文を実行する
SQL Serverのテーブルの列名を取得するには、SQL Serverのテーブルの列名を取得するSELECT文を実行します。
SQL Serverのテーブルの列名を取得するSELECT文は次の通りです。
SELECT c.name FROM sys.objects t INNER JOIN sys.columns c ON t.object_id = C.object_id WHERE t.type = 'U' AND t.name = 'tbl_data_list' Order BY C.column_id
このSELECT文をマクロが実行すると列名が取得できます。
ちなみに、SQL Serverに直接SQL Serverのテーブルの列名を取得するSELECT文を実行した結果は次の通りです。
テーブルtbl_data_listの列名が取得できています。
SQL Serverのテーブルの列名を取得する方法
SQL Serverのテーブルの列名を取得するには、次の流れの通りにコードを書いていきます。
Excelのマクロ
このインスタンスは、マクロがSQL Serverに接続するために必要です。
このインスタンスは、SQL Serverのテーブルデータの参照・挿入に必要です。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
列名の取得は、列名が取得できるSELECT文を用意し、Getrowsメソッドを実行することで取得することができます。
コードの例
Excelのマクロのコード(例)
Dim DBName As String 'データベース名 Dim connDB As String 'データベース接続情報 Dim tblNM 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 'DBの名前 DBName = "testDataDB" 'データを取得するテーブル名を取得 tblNM = "tbl_data_list" '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" sqlStr = sqlStr & " c.name " sqlStr = sqlStr & " FROM" sqlStr = sqlStr & " sys.objects t" sqlStr = sqlStr & " INNER JOIN sys.columns c ON " sqlStr = sqlStr & " t.object_id = C.object_id " sqlStr = sqlStr & " WHERE " sqlStr = sqlStr & " t.type = 'U'" sqlStr = sqlStr & " AND t.name='" & tblNM & "' " sqlStr = sqlStr & " Order BY " sqlStr = sqlStr & " C.column_id " 'データを抽出する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
コードの解説
注目すべきコード①
最初に見て頂きたいのは41行目から60行目です。
'テーブルの列名を取得する sqlStr = "SELECT" sqlStr = sqlStr & " c.name " sqlStr = sqlStr & " FROM" sqlStr = sqlStr & " sys.objects t" sqlStr = sqlStr & " INNER JOIN sys.columns c ON " sqlStr = sqlStr & " t.object_id = C.object_id " sqlStr = sqlStr & " WHERE " sqlStr = sqlStr & " t.type = 'U'" sqlStr = sqlStr & " AND t.name='" & tblNM & "' " sqlStr = sqlStr & " Order BY " sqlStr = sqlStr & " C.column_id " 'データを抽出するSQL文をSourceプロパティに設定する oRS.Source = sqlStr '指定したテーブルのデータを参照する(アクセスする) oRS.Open 'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納) getVal = oRS.GetRows
41行目から60行目でSQL Serverのテーブルの列名を取得するSELECT文を用意し、そのSELECT文を54行目のsourceプロパティに設定します。
次に57行目でrecordsetのOpenメソッドを実行してカーソルを開いた後に、60行目のGetRowsメソッドを実行すると、SQL Serverのテーブルの列名を取得することができます。
上記のコードでは取得した列名を配列getValに格納しています。
注目すべきコード②
次に見て頂きたいのは33行目です。
'Recordsetオブジェクトのインスタンスを生成する Set oRS = New ADODB.Recordset
この33行目が実行されるとrecordsetを扱うことができるようになります。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの8行目の「ADODB.Connection」と9行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adoCON As New ADODB.Connection 'Connection用変数 Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、SQL Serverのテーブルの列名を取得する方法についてご説明しました。
SQL Serverのテーブルの列名を取得するSELECT文をマクロが用意し、そのSELECT文を実行すればSQL Serverのテーブルの列名を簡単に取得することができます。
SQL Serverのテーブルの列名を取得したい時に本記事を参考にしていただけたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。