| title | Work With SQL Server PowerShell Paths | Microsoft Docs | |
|---|---|---|
| ms.custom | ||
| ms.date | 03/14/2017 | |
| ms.prod | sql-server-2016 | |
| ms.reviewer | ||
| ms.suite | ||
| ms.technology |
|
|
| ms.tgt_pltfrm | ||
| ms.topic | article | |
| ms.assetid | f31d8e2c-8d59-4fee-ac2a-324668e54262 | |
| caps.latest.revision | 11 | |
| author | JennieHubbard | |
| ms.author | jhubbard | |
| manager | jhubbard |
After you have navigated to a node in a [!INCLUDEssDE] provider path, you can perform work or retrieve information by using the methods and properties from the [!INCLUDEssDE] management object associated with the node.
-
To work on a path node: Listing Methods and Properties, Using Methods and Properties
After you have navigated to a node in a [!INCLUDEssDE] provider path, you can perform two types of actions:
-
You can run Windows PowerShell cmdlets that operate on nodes, such as Rename-Item.
-
You can call the methods from the associated [!INCLUDEssNoVersion] management object model, such as SMO. For example, if you navigate to the Databases node in a path, you can use the methods and properties of the xref:Microsoft.SqlServer.Management.Smo.Database class.
The [!INCLUDEssNoVersion] provider is used to manage the objects in an instance of the [!INCLUDEssDE]. It is not used to work with the data in databases. If you have navigated to a table or view, you cannot use the provider to select, insert, update, or delete data. Use the Invoke-Sqlcmd cmdlet to query or change data in tables and views from the Windows PowerShell environment. For more information, see Invoke-Sqlcmd cmdlet.
Listing Methods and Properties
To view the methods and properties available for specific objects or object classes, use the Get-Member cmdlet.
This example sets a Windows PowerShell variable to the SMO xref:Microsoft.SqlServer.Management.Smo.Database class and lists the methods and properties:
$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar | Get-Member –Type Methods
$MyDBVar | Get-Member -Type Properties
You can also use Get-Member to list the methods and properties that are associated with the end node of a Windows PowerShell path.
This example navigates to the Databases node in a SQLSERVER: path and lists the collection properties:
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-Item . | Get-Member -Type Properties
This example navigates to the AdventureWorks2012 node in a SQLSERVER: path and lists the object properties:
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2012
Get-Item . | Get-Member -Type Properties
Using SMO Methods and Properties
To perform work on objects from a [!INCLUDEssDE] provider path, you can use SMO methods and properties.
This example uses the SMO Schema property to get a list of the tables from the Sales schema in AdventureWorks2012:
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2012\Tables
Get-ChildItem | where {$_.Schema -eq "Sales"}
This example uses the SMO Script method to generate a script that contains the CREATE VIEW statements you must have to re-create the views in AdventureWorks2012:
Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2012\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }
This example uses the SMO Create method to create a database, and then uses the State property to show whether the database exists:
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
SQL Server PowerShell Provider
Navigate SQL Server PowerShell Paths
Convert URNs to SQL Server Provider Paths
SQL Server PowerShell