VBScriptでSQL Serverのデータベース、テーブルの使用サイズを取得する方法

VBScriptでSQL Serverに接続して特定のDBのテーブルの使用量を取得するサンプルプログラムを覚え書き。

テーブルではなくDBのサイズを取得する方法についても記載、その際の補足として複数の結果セットをレコードセットで取得するほう法についても記載しています。

データベースもしくは、テーブルのサイズを取得する
1. 手動確認手順
データベースの使用サイズを取得するサンプルプログラム
1. 補足(複数の結果セットが返される場合)
テーブルの使用サイズを取得するサンプルプログラム
接続文字列について
まとめ

データベースもしくは、テーブルのサイズを取得する

SQL Serverのデータベースやテーブルのサイズを取得する方法としては、SQL Serverのストアドプロシージャ「sp_spaceused」を実行します。

「sp_spaceused」を引数なしで実行すれば、ストアドプロシージャが実行されたデータベースそのものの使用サイズが取得できます。

「sp_spaceused」にテーブル名を指定して実行すれば、指定したテーブルの使用サイズが取得できます。

VBScriptで実行させる前に手動で実行する場合の手順を参考までに記載しておきます。

手動確認手順

「Microsoft SQL Server Management Studio」から対象のデータベースサーバーに接続し、対象のデータベースを右クリック、[新しいクエリ]をクリックする。

新規のクエリウィンドウが表示されるので、「sp_spaceused」と入力し、[実行]ボタンをクリックする。

以下のようにDBの情報が結果として表示されます。
画面を見ればわかりますが、テーブルを指定しない場合2つの結果セットが返ってきます。

テーブル単体の情報が取得したい場合は、「sp_spaceused テーブル名」を入力し、[実行]ボタンをクリックします。
※以下は、NorthwindデータベースのEmployeesテーブルを指定した場合の例

Employeesテーブルの情報が表示されます。

実行結果の各フィールドの内容については、Microsoft の以下のページを参照してください。

sp_spaceused (Transact-SQL) - SQL Server

sp_spaceusedには、データベース内のオブジェクトによって使用される行数、予約済みディスク領域、およびディスク領域が表示されます。

データベースの使用サイズを取得するサンプルプログラム

デーベースの使用サイズ(databese_size)をメッセージボックスで表示するサンプルプログラムを以下に記載します。

Dim strCon, objCon, objRec, objCmd

'接続文字列の作成
strCon = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=接続するサーバー名もしくはIPアドレス;" _
& "DATABASE=Northwind;UID=sa;PWD=saユーザーのパスワード;"

Set objRec = CreateObject("ADODB.Recordset")
Set objCon = CreateObject("ADODB.Connection")

    objCon.ConnectionString = strCon
    objCon.CursorLocation = 2 'adUseServer
    objCon.Open

Set objCmd = CreateObject("ADODB.Command")
    objCmd.CommandText = "sp_spaceused"
    objCmd.CommandType = 4 'adCmdStoredProc
    objCmd.ActiveConnection = objCon

Set objRec = objCmd.Execute

MsgBox objRec("database_size")

データベースのサイズがメッセージボックスにMB単位(文字列データ)で表示されます。

補足(複数の結果セットが返される場合)

「sp_spaceused」にテーブルを指定しない場合、データベースの情報が2つの結果セットとして返されます。

1つ目の結果セットは、"database_name"、"database_size"、"unallocated space"の3つのフィールドが含まれた結果セットです。

2つ目の結果セットは、"reserved"、"data"、"index_size"、"unused"の4つのフィールドが含まれた結果セットです。

例えば、2つ目の結果セットに含まれるフィールドの値を取得したい場合は、"NextRecordset"を使うことで取得が可能になります。

以下に「sp_spaceused」の実行結果の2つ目の結果セットに含まれる"reserved"の値を表示するサンプルプログラムを記載します。

Dim strCon, objCon, objRec, objCmd

'接続文字列の作成
strCon = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=接続するサーバー名もしくはIPアドレス;" _
& "DATABASE=Northwind;UID=sa;PWD=saユーザーのパスワード;"

Set objRec = CreateObject("ADODB.Recordset")
Set objCon = CreateObject("ADODB.Connection")

	objCon.ConnectionString = strCon
	objCon.CursorLocation = 2 'adUseServer
	objCon.Open

Set objCmd = CreateObject("ADODB.Command")
	objCmd.CommandText = "sp_spaceused"
	objCmd.CommandType = 4 'adCmdStoredProc
	objCmd.ActiveConnection = objCon

Set objRec = objCmd.Execute
Set objRec = objRec.NextRecordset

MsgBox objRec("reserved")

ストアドプロシージャを指定したコマンドオブジェクトを実行し、結果セットをRecordsetオブジェクトにセット、さらにそのRecordsetオブジェクトに対して"NextRecordset"で2つ目の結果セットをRecordsetオブジェクトにセットし直す感じです。

"NextRecordset"を実行することでRecordsetオブジェクトの中身が2つ目の結果セットになります。

テーブルの使用サイズを取得するサンプルプログラム

テーブルの場合は、ストアドプロシージャ「sp_spaceused」に対象のテーブル名を引数として渡す必要があります。

VBScriptからSQL Serverのストアドプロシージャに引数を渡して実行する場合は、Commandオブジェクトの"Parameters"を使います。

以下がそのサンプルプログラムになります。

Dim strCon, objCon, objRec, objCmd

'接続文字列の作成
strCon = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=接続するサーバー名もしくはIPアドレス;" _
& "DATABASE=Northwind;UID=sa;PWD=saユーザーのパスワード;"

Set objRec = CreateObject("ADODB.Recordset")
Set objCon = CreateObject("ADODB.Connection")

    objCon.ConnectionString = strCon
    objCon.CursorLocation = 2 'adUseServer
    objCon.Open

Set objCmd = CreateObject("ADODB.Command")
    objCmd.CommandText = "sp_spaceused"
    objCmd.CommandType = 4 'adCmdStoredProc
    objCmd.ActiveConnection = objCon
        objCmd.Parameters(1).Value = "employees"

Set objRec = objCmd.Execute

msgbox objRec("data")

実行するとEmployeesテーブルの使用量がKB単位(文字列データ)で表示されます。

接続文字列について

上記の2つのサンプルプログラムでは、SQL Serverへの接続の際に「sa」ユーザーを使用したSQL Server認証で接続するようになっています。

もしSQL Server認証ではなく、Windows認証モードで接続したい場合の接続文字列は以下のようになります。

Dim strCon, objCon, objRec, objCmd

'接続文字列の作成
strCon = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=localhost;" _
& "DATABASE=Northwind;Trusted_Connection=yes;"

上記はODBCプロバイダー接続の場合の接続文字列になります。

接続文字列にについては、接続に使うプロバイダーによって異なりますので注意してください。

接続文字列を簡単に作る方法を別の記事で紹介していますので、是非参考にしてください。

ODBC:udlファイルを使用して接続文字列を簡単に作成する方法

VBやVBAなどでデータベースに接続するプログラムを作る際に接続文字列を打ち込むのはとてもめんどう。 "udl"ファイルを使えばダイアログボックス上で設定していけば簡単に接続文字列を作ってくれる。接続文字列作成のためのウィザードみたいな...

まとめ

VBScriptを使用してSQL Serverのデータベースと特定のテーブルの現在の使用サイズを取得するサンプルプログラムを紹介しました。

ADOでSQL Serverのデータベースに接続、SQL Serverのストアドプロシージャを呼び出して値を取得します。

ストアドプロシージャに引数を渡す場合と渡さない場合とで若干プログラムが変わること、また今回使用したストアドプロシージャ「sp_spaceused」は実行結果がレコードセットとして取得できるのでサンプルプログラムのようなコードになります。

ストアドプロシージャによっては、結果がレコードセットではなかったりするものもあるので注意してください。