Skip to content

Latest commit

 

History

History
101 lines (70 loc) · 4.92 KB

work-with-sql-server-powershell-paths.md

File metadata and controls

101 lines (70 loc) · 4.92 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic
Work With SQL Server PowerShell Paths
Learn how to manipulate and retrieve information using either cmdlets or the methods and properties of the object identified by the provider path.
markingmyname
maghan
matteot, drskwier
03/14/2017
sql
sql-server-powershell
conceptual

Work With SQL Server PowerShell Paths

[!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

Listing Methods and Properties

To view the methods and properties available for specific objects or object classes, use the Get-Member cmdlet.

Examples: Listing Methods and Properties

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 [!INCLUDE sssampledbobject-md] node in a SQLSERVER: path and lists the object properties:

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

Using Methods and Properties

Using SMO Methods and Properties

To perform work on objects from a [!INCLUDEssDE] provider path, you can use SMO methods and properties.

Examples: Using Methods and Properties

This example uses the SMO Schema property to get a list of the tables from the Sales schema in [!INCLUDE sssampledbobject-md]:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2022\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 [!INCLUDE sssampledbobject-md]:

Remove-Item C:\PowerShell\CreateViews.sql  
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2022\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

See Also