使用 SQL Server PowerShell 提供者

SQL Server PowerShell 階層

可以在階層中表示資料或物件模型的產品會使用 Windows PowerShell 提供者來公開階層。這個階層是使用與 Windows 檔案系統所使用之磁碟機和路徑結構類似的結構公開。

每個 Windows PowerShell 提供者都會實作一或多個磁碟機，每一個磁碟機都是相關物件階層的根節點。SQL Server 提供者會實作 SQLSERVER: 磁碟機。SQLSERVER: 磁碟機有四個主要資料夾。每個資料夾及其子資料夾都代表可使用 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 格式來指定物件名稱。

這是本機電腦上預設 Database Engine 執行個體中 AdventureWorks2008R2 資料庫之 Purchasing 結構描述的 Vendor 資料表路徑：

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

如需有關 SMO 物件模型階層的詳細資訊，請參閱＜SMO 物件模型圖表＞。

路徑中的物件類別節點會與相關聯物件模型中的集合類別產生關聯。物件名稱節點會與相關聯物件模型中的物件類別產生關聯，如下表所示。

每當您參考路徑中的 Database Engine 執行個體時，SQL Server 提供者都會使用 SMO 開啟此執行個體的 Windows 驗證連接。此連接是使用執行 Windows PowerShell 工作階段的 Windows 帳戶認證來進行。SQL Server 提供者不會使用 SQL Server 驗證。

導覽 SQL Server 路徑

Windows PowerShell 會實作指令程式來導覽提供者階層，並針對目前的物件執行基本作業。由於指令程式會經常被使用，所以具有簡短、標準的別名。也有一組別名會將指令程式對應到類似的命令提示字元命令，而且有另一組別名適用於 UNIX Shell 命令。

SQL Server 提供者會實作提供者指令程式的子集，如下表所示。

例如，您可以使用下列其中一組指令程式或別名來擷取可用的 SQL Server 執行個體清單，方式是導覽至 SQLSERVER:\SQL 資料夾，並要求該資料夾的子項目清單：

• 使用完整的指令程式名稱：

  Set-Location SQLSERVER:\SQL
  Get-ChildItem
  

• 使用標準的別名：

  sl SQLSERVER:\SQL
  gci
  

• 使用 cmd 別名：

  cd SQLSERVER:\SQL
  dir
  

• 使用 UNIX Shell 別名：

  cd SQLSERVER:\SQL
  ls
  

  重要事項
  某些 SQL Server 識別碼 (物件名稱) 包含 Windows PowerShell 在路徑名稱中不支援的字元。如需有關如何使用包含這些字元之名稱的詳細資訊，請參閱＜在 PowerShell 中使用 SQL Server 識別碼＞。

使用 Get-ChildItem

Get-ChildItem (或其 dir 和 ls 別名) 傳回的資訊視您在 SQLSERVER: 路徑中的位置而定。

根據預設，Get-ChildItem 不會列出任何系統物件。使用 Force 參數來查看系統物件，例如 sys 結構描述中的物件。

此範例會列出本機電腦以及您已經進行 SMO 或 WMI 連接的任何電腦：

Set-Location SQLSERVER:\SQL
Get-ChildItem

此範例會列出本機電腦上的 Database Engine 執行個體：

Set-Location SQLSERVER:\SQL\localhost
Get-ChildItem

此範例會列出預設 Database Engine 執行個體中可用之物件的主要類別。此清單包括類似 Endpoints、Certificates 和 Databases 的名稱：

Set-Location SQLSERVER:\SQL\localhost\DEFAULT
Get-ChildItem

此範例會列出預設 Database Engine 執行個體中可用的資料庫。Force 參數可用來加入系統資料庫，例如 master 和 model：

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-ChildItem -force

在路徑節點上執行動作

當您導覽至 Windows PowerShell 路徑中的節點之後，您可以執行兩種動作：

• 您可以執行在節點上運作的 Windows PowerShell 指令程式，例如 Rename-Item。

• 您可以從關聯的 SQL Server 管理物件模型 (如 SMO) 呼叫方法。例如，如果您導覽至路徑中的 Databases 節點，您可以使用 Database 類別的方法和屬性。

SQL Server 提供者是用來管理 Database Engine 執行個體中的物件，而不是用來處理資料庫中的資料。如果您導覽至資料表或檢視表，您就無法使用此提供者來選取、插入、更新或刪除資料。使用 Invoke-Sqlcmd 指令程式可從 Windows PowerShell 環境來查詢或變更資料表和檢視表中的資料。如需詳細資訊，請參閱＜使用 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: 磁碟機來縮短 AdventureWorks2008R2 物件的路徑，例如 Purchasing.Vendor 資料表：

Set-Location AWDB:\Tables\Purchasing.Vendor

管理 SQL Server 驗證連接

根據預設，SQL Server 提供者會使用建立 Database Engine 之 Windows 驗證連接所用的 Windows 帳戶。若要建立 SQL Server 驗證連接，您必須將 SQL Server 登入認證與虛擬磁碟機產生關聯，然後使用變更目錄命令 (cd) 連接到該磁碟機。在 Windows PowerShell 中，安全性認證只能與虛擬磁碟機產生關聯。

這個指令碼會建立名為 sqldrive 的函數，可讓您用來建立與指定之 SQL Server 驗證登入和執行個體相關聯的虛擬磁碟機。

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 命名空間中的類別。這個命名空間最常用來查詢和管理實作每個 Database Engine 執行個體或原則存放區之服務的狀態。

這則範例將示範如何使用 ManagedComputer 類別來停止和啟動執行預設 Database Engine 執行個體的服務。

# 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"

管理 Tab-Completion

Windows PowerShell Tab-Completion 會減少您必須做的輸入量。當您已輸入一部分的路徑或指令程式名稱時，您可以按 Tab 鍵來取得名稱符合您已輸入之項目的項目清單。然後您可以從清單中選取想要的項目，而不必輸入名稱的其餘部分。

如果您正在擁有許多物件的資料庫中工作，索引標籤完成的清單會變得很大。某些 SQL Server 物件類型 (如檢視表) 也會有大量的系統物件。

SQL Server 嵌入式管理單元導入三個系統變數，您可以使用這些變數來控制 Tab-Completion 和 Get-ChildItem 所呈現的資訊量。

• **$SqlServerMaximumTabCompletion =**n
  指定 tab-completion 清單中要包含的最大物件數目。如果您在有 n 個物件以上的路徑節點上選取 Tab 鍵，Tab-Completion 清單就會在 n 處截斷。n 是一個整數。預設值為 0，表示列出的物件數沒有限制。

• **$SqlServerMaximumChildItems =**n
  指定 Get-ChildItem 顯示的最大物件數。如果 Get-ChildItem 在具有 n 個物件以上的路徑節點上執行，此清單會在 n 處截斷。n 是一個整數。預設值為 0，表示列出的物件數沒有限制。

• $SqlServerIncludeSystemObjects = { $True | $False }
  如果為 $True，表示系統物件是由 Tab-Completion 和 Get-ChildItem 所顯示。如果為 $False，表示未顯示任何系統物件。預設設定是 $False。

以下範例會設定所有的三個變數，並列出其設定：

$SqlServerMaximumTabCompletion = 20
$SqlServerMaximumChildItems = 10
$SqlServerIncludeSystemObjects = $False
dir variable:sqlserver*

請參閱

概念

其他資源