PowerShellとSQL Serverの連携でできること：データ取得、操作、自動化まで

2024-05-14

PowerShellでSQL Serverクエリを実行する方法

SQL Serverモジュールは、PowerShellからSQL Serverとやり取りするための公式モジュールです。このモジュールを使用すると、クエリの実行、データの取得、SQL Serverインスタンスの設定変更など、さまざまな操作を実行できます。

モジュールのインストール

SQL Serverモジュールをインストールするには、次のコマンドを実行します。

Install-Module -Name SqlServer

クエリの実行

モジュールをインストールしたら、以下のコマンドを使用してクエリを実行できます。

Invoke-Sqlcmd -ServerInstance <サーバー名> -Database <データベース名> -Query <クエリ>

例

次のコマンドは、AdventureWorksデータベースのCustomersテーブルにあるすべての顧客情報を取得します。

Invoke-Sqlcmd -ServerInstance localhost -Database AdventureWorks -Query "SELECT * FROM Customers"

sqlcmdコマンドラインツールを使用する

sqlcmdは、SQL Serverとコマンドラインでやり取りするためのツールです。PowerShellからこのツールを呼び出すことで、クエリを実行できます。

sqlcmdを実行するには、次のコマンドを実行します。

sqlcmd -S <サーバー名> -d <データベース名> -c "<クエリ>"

sqlcmd -S localhost -d AdventureWorks -c "SELECT * FROM Customers"

どちらの方法を選択するかは、状況によって異なります。SQL Serverモジュールは、PowerShellからSQL Serverとやり取りする際の便利な機能を多く提供します。一方、sqlcmdコマンドラインツールは、よりシンプルな方法でクエリを実行したい場合に役立ちます。

上記に加えて、PowerShellスクリプトを使用して、クエリを自動化することもできます。これは、繰り返し実行する必要があるタスクがある場合に役立ちます。

PowerShellでSQL Serverクエリを実行するサンプルコード

SQL Serverモジュールを使用する

# モジュールのインストール
Install-Module -Name SqlServer

# データベースへの接続
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=localhost;Database=AdventureWorks;Integrated Security=True"
$connection.Open()

# クエリの実行
$command = New-Object System.Data.SqlClient.SqlCommand
$command.Connection = $connection
$command.CommandText = "SELECT * FROM Customers"
$reader = $command.ExecuteReader()

# 結果をCSVファイルにエクスポート
$csv = New-Object System.IO.StreamWriter("C:\Customers.csv")
$csv.WriteLine($reader.GetSchemaTable().Rows | ForEach-Object { $_.Name })

while ($reader.Read()) {
    $row = ""
    for ($i = 0; $i -lt $reader.FieldCount; $i++) {
        $row += $reader[$i] + ","
    }
    $csv.WriteLine($row.Substring(0,$row.Length-1))
}

$reader.Close()
$csv.Close()
$connection.Close()

sqlcmdコマンドラインツールを使用する

sqlcmd -S localhost -d AdventureWorks -c "SELECT * FROM Customers"

上記はほんの一例です。PowerShellとSQL Serverモジュール/sqlcmdコマンドラインツールを使用して、さまざまな操作を実行できます。詳細については、公式ドキュメントを参照してください。

PowerShellでSQL Serverクエリを実行するその他の方法

SMO（SQL Server Management Objects）を使用する

SMOは、.NET Frameworkを使用してSQL Serverとやり取りするためのマネージドコードAPIです。PowerShellを使用してSMOを呼び出すことで、クエリを実行できます。

# SMOアセンブリの読み込み
Add-Type -AssemblyName System.Data.SqlClient

# サーバーへの接続
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("localhost")
$database = $server.Databases["AdventureWorks"]

# クエリの実行
$query = "SELECT * FROM Customers"
$command = New-Object Microsoft.SqlServer.Management.Smo.SqlCommand($query, $database)
$reader = $command.ExecuteReader()

# 結果をCSVファイルにエクスポート
$csv = New-Object System.IO.StreamWriter("C:\Customers.csv")
$csv.WriteLine($reader.GetSchemaTable().Rows | ForEach-Object { $_.Name })

while ($reader.Read()) {
    $row = ""
    for ($i = 0; $i -lt $reader.FieldCount; $i++) {
        $row += $reader[$i] + ","
    }
    $csv.WriteLine($row.Substring(0,$row.Length-1))
}

$reader.Close()
$csv.Close()

PowerShell プロバイダは、PowerShell コマンドレットを使用して、特定のシステムまたはアプリケーションとやり取りできるようにするモジュールです。SQL Server用のPowerShellプロバイダがあり、このプロバイダを使用してクエリを実行できます。

# PowerShell プロバイダのインストール
Install-Module -Name SqlServerProvider

# データベースへの接続
$connection = Get-SqlServerConnection -ServerName localhost -DatabaseName AdventureWorks

# クエリの実行
$query = "SELECT * FROM Customers"
$results = Invoke-SqlCmd -Connection $connection -Query $query

# 結果をCSVファイルにエクスポート
$csv = New-Object System.IO.StreamWriter("C:\Customers.csv")
$csv.WriteLine($results.Schema.Columns | ForEach-Object { $_.Name })

foreach ($row in $results) {
    $csv.WriteLine($row | ForEach-Object { $_ })
}

$csv.Close()

PowerShell Web Servicesモジュールは、PowerShellを使用してWebサービスとやり取りできるようにするモジュールです。SQL Serverには、PowerShellで管理できるWebサービスがいくつかあります。これらのWebサービスを使用して、クエリを実行できます。

# PowerShell Web Servicesモジュールのインストール
Install-Module -Name PowerShellGet

# SQL Server Management Webサービスへの接続
$service = New-WebServiceProxy -Uri http://localhost/ReportingServices/2010/RS.asmx
$credentials = New-Object System.Management.Automation.PSCredential("ReportServerUser", "ReportServerPassword")

# クエリの実行
$report = $service.Render($reportPath, $parameters, $format, $locale, $encoding, $timeout, $dataSourceName, $dataSourceDescription, $credentials)
$csv = New-Object System.IO.StreamWriter("C:\Customers.csv")
$csv.Write($report.Stream)
$csv.Close()