Work with SQL Server PowerShell paths
After you have navigated to a node in a Database Engine provider path, you can perform work or retrieve information by using the methods and properties from the Database Engine management object associated with the node.
Note
There are two SQL Server PowerShell modules; SqlServer and SQLPS.
The SqlServer module is the current PowerShell module to use.
The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.
The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.
Install the SqlServer module from the PowerShell Gallery.
For more information, visit SQL Server PowerShell.
After you navigate to a node in a Database Engine 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 SQL Server 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 SQL Server provider is used to manage the objects in an instance of the Database Engine. 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.
List 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.
Example: 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 AdventureWorks2022 node in a SQLSERVER: path and lists the object properties:
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2022
Get-Item . | Get-Member -Type Properties
Use Methods and Properties
Using SMO Methods and Properties
To perform work on objects from a Database Engine provider path, you can use SMO methods and properties.
Example: Using methods and properties
This example uses the SMO Schema property to get a list of the tables from the Sales schema in AdventureWorks2022:
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 AdventureWorks2022:
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