SQL Server PowerShell プロバイダの使用
SQL Server Windows PowerShell プロバイダは、ファイル システム パスと同様のパスで SQL Server オブジェクトの階層を公開します。このパスを使用してオブジェクトの場所を指定し、SQL Server 管理オブジェクト (SMO) モデルのメソッドを使用してオブジェクトの操作を実行できます。
SQL Server プロバイダの階層
データまたはオブジェクトのモデルを階層で表すことができる製品は、Windows PowerShell プロバイダを使用して階層を公開できます。階層は、Windows ファイル システムで使用されるものに似たドライブおよびパス構造を使用して公開されます。
それぞれの Windows PowerShell プロバイダは 1 つ以上のドライブを実装します。各ドライブは、関連するオブジェクトの階層のルート ノードです。SQL Server プロバイダは、SQLSERVER: ドライブを実装します。SQLSERVER: ドライブには 4 つの主要フォルダがあります。各フォルダおよびそのサブフォルダは、SQL Server 管理オブジェクト モデルを使用してアクセスできるオブジェクトのセットを表します。これらのいずれかの主要フォルダで始まるパスでサブフォルダにフォーカスを設定すると、関連付けられたオブジェクト モデルのメソッドを使用して、このノードによって表されるオブジェクトの操作を実行できます。SQL Server 2008 プロバイダが実装する Windows PowerShell フォルダを次の表に示します。
フォルダ |
SQL Server オブジェクト モデルの名前空間 |
オブジェクト |
---|---|---|
SQLSERVER:\SQL |
Microsoft.SqlServer.Management.Smo Microsoft.SqlServer.Management.Smo.Agent |
データベース オブジェクト (テーブル、ビュー、ストアド プロシージャなど) |
SQLSERVER:\SQLPolicy |
ポリシー ベースの管理オブジェクト (ポリシーやファセットなど) |
|
SQLSERVER:\SQLRegistration |
登録済みサーバー オブジェクト (サーバー グループや登録済みサーバーなど) |
|
SQLSERVER:\DataCollection |
コレクション セットや構成ストアなどのデータ コレクタ オブジェクト |
たとえば、SQLSERVER:\SQL フォルダを使用して、SMO オブジェクト モデルでサポートされる任意のオブジェクトを表すパスを開始することができます。SQLSERVER:\SQL パスの先頭部分は、SQLSERVER:\SQL\ComputerName\InstanceName です。コンピュータ名は必ず指定します。ローカル コンピュータの場合は localhost または `(local`) と指定できます。既定のインスタンスの場合でも、常にインスタンス名を指定する必要があります。既定のインスタンスには、DEFAULT を指定します。インスタンス名の後のノードは、オブジェクト クラス (Database や View など) とオブジェクト名 (AdventureWorks など) で切り替わります。スキーマはオブジェクト クラスとしては表されません。スキーマ内の最上位レベルのオブジェクト (テーブルやビューなど) のノードを指定する場合、オブジェクト名を SchemaName.ObjectName の形式で指定する必要があります。
ローカル コンピュータ上のデータベース エンジンの既定のインスタンスにある AdventureWorks データベースの Purchasing スキーマ内の Vendor テーブルのパスは次のようになります。
SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables\Purchasing.Vendor
SMO オブジェクト モデルの階層の詳細については、「SMO オブジェクト モデル ダイアグラム」を参照してください。
パスに含まれるオブジェクト クラス ノードは、関連付けられたオブジェクト モデルのコレクション クラスに関連付けられます。オブジェクト名ノードは、次の表に示すように、関連付けられたオブジェクト モデルのオブジェクト クラスに関連付けられます。
パス |
SMO クラス |
---|---|
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases |
|
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks |
データベース エンジンのインスタンスをパスで参照すると、SQL Server プロバイダでは、常に SMO を使用してそのインスタンスに対する Windows 認証接続を開きます。この接続は、Windows PowerShell セッションを実行している Windows アカウントの資格情報を使用して確立されます。SQL Server プロバイダでは、SQL Server 認証は使用されません。
SQL Server パスの操作
Windows PowerShell では、コマンドレットを実装して、プロバイダ階層内を移動したり現在のオブジェクトの基本的な操作を実行します。コマンドレットは頻繁に使用されるため、短い標準の別名が用意されています。また、コマンドレットを類似のコマンド プロンプトのコマンドにマップする別名のセットと、UNIX シェル コマンド用の別のセットもあります。
SQL Server プロバイダは、次の表に示すように、プロバイダのコマンドレットのサブセットを実装します。
コマンドレット |
標準の別名 |
コマンドの別名 |
UNIX シェルの別名 |
説明 |
---|---|---|---|---|
Get-Location |
gl |
pwd |
pwd |
現在のノードを取得します。 |
Set-Location |
sl |
cd、chdir |
cd、chdir |
現在のノードを変更します。 |
Get-ChildItem |
gci |
dir |
ls |
現在のノードに格納されているオブジェクトの一覧を表示します。 |
Get-Item |
gi |
現在のアイテムのプロパティを返します。 |
||
Move-Item |
mi |
move |
mv |
オブジェクトを移動します。 |
Rename-Item |
rni |
rn |
ren |
オブジェクトの名前を変更します。 |
Remove-Item |
ri |
del、rd |
rm、rmdir |
オブジェクトを削除します。 |
たとえば、次に示す一連のコマンドレットまたは別名のいずれかを使用して、SQLSERVER:\SQL フォルダに移動しフォルダの子アイテムの一覧を要求することによって、使用できる SQL Server インスタンスの一覧を取得することができます。
完全なコマンドレット名を使用します。
Set-Location SQLSERVER:\SQL Get-ChildItem
標準の別名を使用します。
sl SQLSERVER:\SQL gci
コマンドの別名を使用します。
cd SQLSERVER:\SQL dir
UNIX シェルの別名を使用します。
cd SQLSERVER:\SQL ls
重要 一部の SQL Server 識別子 (オブジェクト名) には、Windows PowerShell のパス名ではサポートされない文字が含まれている場合があります。それらの文字を含む名前の使用方法の詳細については、「PowerShell での SQL Server 識別子の使用」を参照してください。
Get-ChildItem の使用
Get-ChildItem (またはその別名の dir および ls) で返される情報は、SQLSERVER: パス内の場所によって異なります。
パスの場所 |
Get-ChildItem の結果 |
---|---|
SQLSERVER:\SQL |
ローカル コンピュータの名前を返します。SMO または WMI を使用して他のコンピュータ上のデータベース エンジンのインスタンスに接続している場合は、それらのコンピュータも一覧表示されます。 |
SQLSERVER:\SQL\ComputerName |
コンピュータ上のデータベース エンジンのインスタンスの一覧。 |
SQLSERVER:\SQL\ComputerName\InstanceName |
インスタンス内の最上位レベルのオブジェクトの種類の一覧 (Endpoints、Certificates、Databases など)。 |
オブジェクト クラスのノード (Databases など) |
その種類のオブジェクトの一覧 (データベースの場合は master、model、AdventureWorks など)。 |
オブジェクト名のノード (AdventureWorks など) |
オブジェクト内に格納されているオブジェクトの種類の一覧。たとえば、データベースの場合はテーブルやビューなどのオブジェクトの種類が一覧表示されます。 |
既定では、Get-ChildItem でシステム オブジェクトは一覧表示されません。システム オブジェクト (たとえば sys スキーマ内のオブジェクト) を表示するには、Force パラメータを使用します。
次の例では、ローカル コンピュータと、SMO または WMI 接続を確立しているコンピュータを一覧表示します。
Set-Location SQLSERVER:\SQL
Get-ChildItem
次の例では、ローカル コンピュータ上にあるデータベース エンジンのインスタンスを一覧表示します。
Set-Location SQLSERVER:\SQL\localhost
Get-ChildItem
次の例では、データベース エンジンの既定のインスタンスで使用できるオブジェクトの主要なクラスを一覧表示します。一覧には、Endpoints、Certificates、Databases などの名前が含まれます。
Set-Location SQLSERVER:\SQL\localhost\DEFAULT
Get-ChildItem
次の例では、データベース エンジンの既定のインスタンスで使用できるデータベースを一覧表示します。ここでは、master や model などのシステム データベースを表示するために、Force パラメータが使用されています。
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-ChildItem -force
パス ノードでの操作の実行
Windows PowerShell パスでノードに移動した後、2 種類の操作を実行できます。
Rename-Item など、ノードを操作する Windows PowerShell コマンドレットを実行できます。
関連付けられた SQL Server 管理オブジェクト モデル (SMO など) のメソッドを呼び出すことができます。たとえば、パスで Databases ノードに移動すると、Database クラスのメソッドとプロパティを使用できます。
SQL Server プロバイダは、データベース エンジンのインスタンスのオブジェクトを管理するために使用されます。データベース内のデータの処理には使用されません。テーブルまたはビューに移動した場合に、プロバイダを使用してデータの選択、挿入、更新、または削除を行うことはできません。テーブルおよびビューのデータを Windows PowerShell 環境からクエリまたは変更するには、Invoke-Sqlcmd コマンドレットを使用します。詳細については、「Invoke-Sqlcmd コマンドレットの使用」を参照してください。
メソッドとプロパティの一覧表示
Get-Member コマンドレットを使用して、特定のオブジェクトまたはオブジェクト クラスで使用できるメソッドとプロパティを表示できます。
次の例は、SMO Database クラスのメソッドの一覧を表示する方法を示しています。
[Microsoft.SqlServer.Management.SMO.Database] | Get-Member -Type Methods
次の例では、Windows PowerShell 変数に SMO Database クラスを設定し、プロパティを一覧表示します。
$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar | Get-Member -Type Properties
また、Get-Member を使用して、Windows PowerShell パスの終了ノードに関連付けられているメソッドとプロパティを一覧表示することもできます。
次の例では、SQLSERVER: パスで Databases ノードに移動し、コレクションのプロパティを一覧表示します。
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-Item . | Get-Member -Type Properties
次の例では、SQLSERVER: パスで AdventureWorks ノードに移動し、オブジェクトのプロパティを一覧表示します。
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks
Get-Item . | Get-Member -Type Properties
メソッドとプロパティの使用
SMO のプロパティを Windows PowerShell コマンドで参照することができます。次の例では、SMO の Schema プロパティを使用して、AdventureWorks の Sales スキーマからテーブルの一覧を取得します。
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables
Get-ChildItem | where {$_.Schema -eq "Sales"}
次の例では、SMO の Script メソッドを使用して、AdventureWorks でビューを再作成するために必要な CREATE VIEW ステートメントを含むスクリプトを生成します。
Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }
次の例では、SMO の Create メソッドを使用してデータベースを作成し、State プロパティを使用してデータベースが存在するかどうかを確認します。
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar.Parent = (Get-Item ..)
$MyDBVar.Name = "NewDB"
$MyDBVar.Create()
$MyDBVar.State
カスタム ドライブの定義
Windows PowerShell では、Windows PowerShell ドライブまたは ps ドライブと呼ばれる仮想ドライブをユーザーが定義できます。これらのドライブには、パス ステートメントの開始ノードがマップされます。これらは、通常、頻繁に入力されるパスを短縮するために使用します。SQLSERVER: パスは長くなることがあり、長くなるとその分 Windows PowerShell ウィンドウ内の領域が使用され、多くの入力が必要になります。特定のパス ノードで多くの作業を行う場合は、そのノードにマップされる Windows PowerShell のカスタム ドライブを定義することができます。たとえば、AdventureWorks データベースで多くの作業を行う場合、AWDB: というドライブを作成できます。
New-PSDrive -Name AWDB -Root SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks
その後、作成した AWDB: ドライブを使用して、Purchasing.Vendor テーブルなどの AdventureWorks オブジェクトへのパスを短くすることができます。
Set-Location AWDB:\Tables\Purchasing.Vendor
SQL Server 認証接続の管理
既定では、SQL Server プロバイダは、それが実行されている Windows アカウントを使用して、データベース エンジンへの Windows 認証接続を行います。SQL Server 認証接続を行うには、SQL Server ログイン資格情報を仮想ドライブに関連付けた後、ディレクトリの変更コマンド (cd) を使用してそのドライブに接続する必要があります。Windows PowerShell では、セキュリティ資格情報は仮想ドライブにのみ関連付けることができます。
次のスクリプトは、指定された SQL Server 認証ログインおよびインスタンスに関連付けられる仮想ドライブを作成するための、sqldrive という名前の関数を作成します。
function sqldrive
{
param( [string]$name, [string]$login = "MyLogin", [string]$root = "SQLSERVER:\SQL\MyComputer\MyInstance" )
$pwd = read-host -AsSecureString -Prompt "Password"
$cred = new-object System.Management.Automation.PSCredential -argumentlist $login,$pwd
New-PSDrive $name -PSProvider SqlServer -Root $root -Credential $cred -Scope 1
}
次に、以下のコマンドを実行して、SQLAuth: という名前の仮想ドライブを作成します。
sqldrive SQLAuth
sqldrive 関数は、ログインの際にパスワードの入力を求めます。入力されたパスワードはマスクされます。ディレクトリの変更コマンド (cd) で SQLAuth: ドライブを使用してパスに接続する場合、操作はすべて、このドライブを作成したときに指定した SQL Server 認証ログイン資格情報を使用して実行されます。
Microsoft.SqlServer.Managment.Smo.Wmi 名前空間の使用
\SQL、\SQLPolicy、および \SQLRegistration の各フォルダに関連付けられている SQL Server 管理オブジェクト モデルの名前空間に加え、Microsoft.SqlServer.Management.Smo.Wmi 名前空間内のクラスを使用することができます。この名前空間は、通常、データベース エンジンまたはポリシー ストアの各インスタンスを実装するサービスの状態をクエリおよび管理するために使用します。
ManagedComputer クラスを使用してデータベース エンジンの既定のインスタンスを実行するサービスを停止および開始する方法を次の例に示します。
# Get a reference to the ManagedComputer class.
cd SQLSERVER:\SQL\localhost
$Wmi = (get-item .).ManagedComputer
# Display the object properties.
$Wmi
# Get a reference to the default instance of the Database Engine.
$DfltInstance = $Wmi.Services["MSSQLSERVER"]
# Display the state of the service.
$DfltInstance
# Stop the service.
$DfltInstance.Stop(); write-host "Stopped"
# Refresh the cache and look at the state.
$DfltInstance.Refresh(); $DfltInstance
# Start the service again.
$DfltInstance.Start(); write-host "Started"
注意 |
---|
この名前空間のクラスをリモート コンピュータに対して使用する場合は、WMI DCOM 接続を許可するように Windows ファイアウォールを構成する必要があります。詳細については、「SQL Server のアクセスを許可するための Windows ファイアウォールの構成」を参照してください。 |
タブ補完の管理
Windows PowerShell のタブ補完を使用すると、必要な入力操作を減らすことができます。パスやコマンドレット名の一部を入力して Tab キーを押すと、既に入力した部分に一致する名前のアイテムの一覧を取得できます。名前の残りの部分を入力しなくても、その一覧からアイテムを選択できます。
多数のオブジェクトを含むデータベースで作業する場合、タブ補完の一覧が非常に大きくなる可能性があります。また、ビューなどの一部の種類の SQL Server オブジェクトには、多数のシステム オブジェクトが含まれます。
SQL Server スナップインでは、タブ補完および Get-ChildItem で表示される情報の量を制御するために使用できる 3 つのシステム変数が導入されています。
**$SqlServerMaximumTabCompletion =**n
タブ補完の一覧に含めるオブジェクトの最大数を指定します。n を超える数のオブジェクトが含まれるパス ノードで Tab キーを押した場合、タブ補完の一覧が n 件までで切り捨てられます。n は整数です。既定の設定は 0 で、これは一覧表示されるオブジェクトの数に制限がないことを示します。**$SqlServerMaximumChildItems =**n
Get-ChildItem で表示されるオブジェクトの最大数を指定します。n を超える数のオブジェクトが含まれるパス ノードで Get-ChildItem を実行した場合、一覧が n 件までで切り捨てられます。n は整数です。既定の設定は 0 で、これは一覧表示されるオブジェクトの数に制限がないことを示します。$SqlServerIncludeSystemObjects = { $True | $False }
$True の場合、タブ補完と Get-ChildItem でシステム オブジェクトが表示されます。$False の場合、システム オブジェクトは表示されません。既定の設定は $False です。
次の例では、3 つすべての変数を設定し、設定を一覧表示します。
$SqlServerMaximumTabCompletion = 20
$SqlServerMaximumChildItems = 10
$SqlServerIncludeSystemObjects = $False
dir variable:sqlserver*
変更履歴
変更内容 |
---|
Smo.Database を参照するコード ブロックの構文エラーを修正しました。SQLSERVER: ドライブに置き換えられた SQL: ドライブへの参照を削除しました。 |