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.
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
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:
This code returns a list of the providers currently enabled in your Windows PowerShell session:
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:
sqlcmd variables and commands. For information about these variables and commands, see the Remarks section of sqlcmd Utility.
Transact-SQL statements. For more information about the Transact-SQL language, see Transact-SQL Reference (Database Engine).
XQuery statements. For more information about the XQuery language supported by SQL Server, see XQuery Language Reference (Database Engine).
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 AdventureWorks2008R2 database in a default instance of the Database Engine:
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
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
The documentation for the object models for the different drives are: