SQL Server PowerShell プロバイダーの使用

SQL Server PowerShell の階層

データまたはオブジェクトのモデルを階層で表すことができる製品は、Windows PowerShell プロバイダーを使用して階層を公開できます。階層は、Windows ファイル システムで使用されるものに似たドライブおよびパス構造を使用して公開されます。

それぞれの Windows PowerShell プロバイダーは 1 つ以上のドライブを実装します。各ドライブは、関連するオブジェクトの階層のルート ノードです。SQL Server プロバイダーは、SQLSERVER: ドライブを実装します。SQLSERVER: ドライブには 4 つの主要フォルダーがあります。各フォルダーおよびそのサブフォルダーは、SQL Server 管理オブジェクト モデルを使用してアクセスできるオブジェクトのセットを表します。これらのいずれかの主要フォルダーで始まるパスでサブフォルダーにフォーカスを設定すると、関連付けられたオブジェクト モデルのメソッドを使用して、このノードによって表されるオブジェクトの操作を実行できます。SQL Server 2008 R2 プロバイダーが実装する Windows PowerShell フォルダーを次の表に示します。

たとえば、SQLSERVER:\SQL フォルダーを使用して、SMO オブジェクト モデルでサポートされる任意のオブジェクトを表すパスを開始することができます。SQLSERVER:\SQL パスの先頭部分は、SQLSERVER:\SQL\ComputerName\InstanceName です。コンピューター名は必ず指定します。ローカル コンピューターの場合は localhost または `(local`) と指定できます。既定のインスタンスの場合でも、常にインスタンス名を指定する必要があります。既定のインスタンスには、DEFAULT を指定します。インスタンス名の後のノードは、オブジェクト クラス (Database、View など) とオブジェクト名 (AdventureWorks2008R2 など) で切り替わります。スキーマはオブジェクト クラスとしては表されません。スキーマ内の最上位レベルのオブジェクト (テーブルやビューなど) のノードを指定する場合、オブジェクト名を SchemaName.ObjectName の形式で指定する必要があります。

ローカル コンピューター上のデータベース エンジンの既定のインスタンスにある AdventureWorks2008R2 データベースの Purchasing スキーマ内の Vendor テーブルのパスは次のようになります。

SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Tables\Purchasing.Vendor

SMO オブジェクト モデルの階層の詳細については、「SMO オブジェクト モデル ダイアグラム」を参照してください。

パスに含まれるオブジェクト クラス ノードは、関連付けられたオブジェクト モデルのコレクション クラスに関連付けられます。オブジェクト名ノードは、次の表に示すように、関連付けられたオブジェクト モデルのオブジェクト クラスに関連付けられます。

データベース エンジンのインスタンスをパスで参照すると、SQL Server プロバイダーでは、常に SMO を使用してそのインスタンスに対する Windows 認証接続を開きます。この接続は、Windows PowerShell セッションを実行している Windows アカウントの資格情報を使用して確立されます。SQL Server プロバイダーでは、SQL Server 認証は使用されません。

SQL Server パスの操作

Windows PowerShell では、コマンドレットを実装して、プロバイダー階層内を移動したり現在のオブジェクトの基本的な操作を実行します。コマンドレットは頻繁に使用されるため、短い標準の別名が用意されています。また、コマンドレットを類似のコマンド プロンプトのコマンドにマップする別名のセットと、UNIX シェル コマンド用の別のセットもあります。

SQL Server プロバイダーは、次の表に示すように、プロバイダーのコマンドレットのサブセットを実装します。

たとえば、次に示す一連のコマンドレットまたは別名のいずれかを使用して、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 でシステム オブジェクトは一覧表示されません。システム オブジェクト (たとえば 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 コマンドレットの使用」を参照してください。

メソッドとプロパティの一覧表示

$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar | Get-Member –Type Methods
$MyDBVar | Get-Member -Type Properties

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-Item . | Get-Member -Type Properties

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2
Get-Item . | Get-Member -Type Properties

メソッドとプロパティの使用

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Tables
Get-ChildItem | where {$_.Schema -eq "Sales"}

Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }

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 では、"PowerShell ドライブ" と呼ばれる仮想ドライブをユーザーが定義できます。これらのドライブには、パス ステートメントの開始ノードがマップされます。これらは、通常、頻繁に入力されるパスを短縮するために使用します。SQLSERVER: パスは長くなることがあり、長くなるとその分 Windows PowerShell ウィンドウ内の領域が使用され、多くの入力が必要になります。特定のパス ノードで多くの作業を行う場合は、そのノードにマップされる Windows PowerShell のカスタム ドライブを定義することができます。たとえば、AdventureWorks2008R2 データベースで多くの作業を行う場合、AWDB: というドライブを作成できます。

New-PSDrive -Name AWDB -Root SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2

その後、作成した AWDB: ドライブを使用して、Purchasing.Vendor テーブルなどの AdventureWorks2008R2 オブジェクトへのパスを短くすることができます。

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*

関連項目

概念

その他の技術情報