Export (0) Print
Expand All

SQL Server PowerShell Help

There are several sources of information about using the SQL Server provider for Windows PowerShell and cmdlets. This includes the help that is available in the Windows PowerShell environment.

For resources you can use to learn Windows PowerShell, see Learning PowerShell.

For an overview of the SQL Server cmdlets and provider, see SQL Server PowerShell Overview.

Use the Get-Help cmdlet to get help in the Windows PowerShell environment. Get-Help provides basic help for the Windows PowerShell language and the various cmdlets and providers available in Windows PowerShell.

The SQL Server cmdlets support Get-Help. Get-Help returns cmdlet usage information, such as syntax and parameter descriptions. The following examples returns the full help for Invoke-Sqlcmd, help for just the parameters, and then help for just the examples:

Get-Help Invoke-Sqlcmd -Full
Get-Help Invoke-Sqlcmd -Parameter *
Get-Help Invoke-Sqlcmd -Examples
Get-Help Invoke-Sqlcmd -Full
Get-Help Invoke-Sqlcmd -Parameter *
Get-Help Invoke-Sqlcmd -Examples

For more information on the ways you can use Get-Help to learn about cmdlets, see Get-Help: Getting Help.

The SQL Server provider also supports Get-Help. For example, this code returns basic information about the SQL Server provider:

Get-Help SQLServer
Get-Help SQLServer

This code returns a list of the providers currently enabled in your Windows PowerShell session:

Get-Help -Category provider
Get-Help -Category provider

For more information about getting provider help in Windows PowerShell, see Drives and Providers.

The Invoke-Sqlcmd cmdlet takes as input any query or script file that can be run by the sqlcmd utility. You can use Get-Help to get information about Invoke-Sqlcmd and its parameters, but there is no Get-Help coverage for the sqlcmd queries.

The -Query or -QueryFromFile input can contain:

The SQL Server provider exposes the hierarchy of SQL Server objects in paths similar to file system paths. For example, this is the path to the Vendor table in the Purchasing schema of the AdventureWorks database in a default instance of the Database Engine:

SQL:\MyComputer\DEFAULT\Databases\AdventureWorks\Tables\Purchasing.Vendor
SQL:\MyComputer\DEFAULT\Databases\AdventureWorks\Tables\Purchasing.Vendor

The provider implements different drive nodes for the objects covered by different SQL Server management object models. Paths with the SQL: drive use the objects covered by the SQL Server Management Objects (SMO), such as tables and views. When you navigate to a node in a path, you can use the object model methods and properties that apply to that type of object.

You can use the Get-Member cmdlet to learn what methods and properties are available for a path node. For example, you can run this code to see the methods supported for the Databases node:

Set-Location SQL:\MyComputer\DEFAULT\Databases
Get-Item . | Get-Member -Type Methods
Set-Location SQL:\MyComputer\DEFAULT\Databases
Get-Item . | Get-Member -Type Methods

This code lists the properties for a variable that has been set to an SMO Table object:

$MyVar = New-Object Microsoft.SqlServer.Management.SMO.Table
$MyVar | Get-Member -Type Properties
$MyVar = New-Object Microsoft.SqlServer.Management.SMO.Table
$MyVar | Get-Member -Type Properties

The documentation for the object models for the different drives are:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft