Excel VBAでデータベースのテーブルのデータサイズを取得・表示する方法

この記事では、Excel VBAを使用してデータベースの特定のテーブルのデータサイズをExcelに表示する方法を詳しく解説します。具体的なコード、その詳細解説、そして応用例を3つ紹介しています。

データベースからデータサイズを取得する基本的な方法

まず、データベースの特定のテーブルのデータサイズをExcelに表示する基本的なVBAコードから始めます。以下のコードは、SQL Serverを想定しています。

Sub GetDataSize()
    Dim cn As Object
    Dim rs As Object
    Dim connStr As String
    Dim queryStr As String
    
    ' 接続文字列を設定
    connStr = "Provider=SQLOLEDB;Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;User ID=YOUR_USER_ID;Password=YOUR_PASSWORD"
    ' クエリを設定
    queryStr = "SELECT t.name AS TableName, SUM(p.reserved_page_count) * 8.0 / 1024 AS DataSizeMB FROM sys.tables t INNER JOIN sys.dm_db_partition_stats p ON t.object_id = p.object_id WHERE t.name = 'YOUR_TABLE_NAME' GROUP BY t.name"

    ' 接続オブジェクトを初期化
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    ' データベースに接続
    cn.Open connStr
    ' クエリを実行
    Set rs = cn.Execute(queryStr)
    
    ' Excelにデータを出力
    Sheets("Sheet1").Range("A1").CopyFromRecordset rs

    ' 接続を閉じる
    rs.Close
    cn.Close
End Sub

コードの詳細解説

このコードでは、まずADODBを使用してデータベースに接続しています。その後、指定されたテーブルのデータサイズを取得するSQLクエリを実行し、その結果をExcelのシートに出力します。

– **connStr**: ここにはデータベースへの接続文字列を設定します。この例ではSQL ServerのOLE DBプロバイダを使用しています。

– **queryStr**: データサイズを取得するSQLクエリを設定しています。このクエリは、指定されたテーブルのデータサイズをメガバイト単位で取得します。

応用例

1. 複数のテーブルのデータサイズを一度に取得

この応用例では、データベース内の複数のテーブルのデータサイズを一度にExcelに出力する方法を解説します。

' クエリを設定
queryStr = "SELECT t.name AS TableName, SUM(p.reserved_page_count) * 8.0 / 1024 AS DataSizeMB FROM sys.tables t INNER JOIN sys.dm_db_partition_stats p ON t.object_id = p.object_id GROUP BY t.name"

2. グラフを使用してデータサイズを視覚的に表示

取得したデータを基に、Excelのグラフ機能を使ってデータサイズを視覚的に表示する方法です。以下のコードは、取得したデータサイズを基に棒グラフを作成します。

Dim ch As Chart

Set ch = Sheets("Sheet1").Shapes.AddChart2(251, xlColumnClustered).Chart
ch.SetSourceData Source:=Sheets("Sheet1").Range("A1:B10")

3. 定期的にデータサイズを取得し、履歴として保存

Task SchedulerやVBAのタイマー機能を使用して、定期的にデータサイズを取得し、別のシートやワークブックに履歴として保存する方法です。

Sub GetAndSaveDataSizeHistory()
    ' 上述のGetDataSize関数を使用してデータを取得
    GetDataSize
    
    ' 新しい日付のデータを別のシートに保存
    Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("History").Range("A" & Sheets("History").Cells(Rows.Count, 1).End(xlUp).Row + 1)
End Sub

まとめ

Excel VBAを使用してデータベースのテーブルのデータサイズを取得する方法について詳しく解説しました。基本的な方法から応用例まで、様々なケースに対応できるような内容となっています。これを機に、Excel VBAを使用して、より高度なデータ解析やレポーティングタスクを行ってみてはいかがでしょうか。