| title | Work With SQL Server PowerShell Paths |
|---|---|
| description | Learn how to manipulate and retrieve information using either cmdlets or the methods and properties of the object identified by the provider path. |
| ms.prod | sql |
| ms.technology | sql-server-powershell |
| ms.topic | conceptual |
| author | markingmyname |
| ms.author | maghan |
| ms.reviewer | matteot, drskwier |
| ms.custom | |
| ms.date | 03/14/2017 |
[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW ]
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.
[!INCLUDE sql-server-powershell-version]
After you navigate 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