Using the SQL Server cmdlets
Windows PowerShell cmdlets are single-function commands that typically have a verb-noun naming convention, such as Get-Help or Set-MachineName. The SQL Server provider for Windows PowerShell supplies cmdlets specific to SQL Server.
SQL Server cmdlets
SQL Server 2008 implements a small number of SQL Server cmdlets.
In the Windows PowerShell environment, the Get-Help cmdlet provides help information for each cmdlet. Get-Help returns information such as the syntax, parameter definitions, input and output types, and a description of the action performed by the cmdlet.
These examples return the basic help, the full help, the syntax diagram, the parameters, and the examples for the SQL Server Encode-SqlName cmdlet:
get-help "Encode-SqlName"
get-help "Encode-SqlName" -Full
get-help "Encode-SqlName" -Syntax
get-help "Encode-SqlName" -Parameter *
get-help "Encode-SqlName" -Examples
Invoke-Sqlcmd
Invoke-Sqlcmd supports running sqlcmd scripts or commands that contain Transact-SQL or XQuery statements. It can accept the sqlcmd input as either a character string input parameter, or as the name of a script file to open. For more information, see Using the Invoke-Sqlcmd cmdlet.
Invoke-PolicyEvaluation
Invoke-PolicyEvaluation reports whether a target set of SQL Server objects comply with the conditions that are defined in policy-based management policies. Optionally, the cmdlet can be used to reconfigure any settable options in the target objects that do not comply with the policy conditions. For more information, see Using the Invoke-PolicyEvaluation cmdlet.
Encoding and Decoding SQL Server Identifiers
Identifiers are the names of SQL Server objects. SQL Server identifiers support a wider range of characters than the Windows PowerShell language. Database Engine bracketed or quoted identifiers have very few restrictions on the characters used in the names. Some of the characters used in bracketed or quoted identifiers cannot be escaped using the Windows PowerShell ` escape character, and can cause problems when using the SQL Server provider.
The Encode-SqlName cmdlet takes as input a SQL Server identifier, and reformats all characters not supported by the Windows PowerShell language with a representation that will work in Windows PowerShell. The Decode-SqlName cmdlet takes as input an encoded SQL Server identifier and returns the original identifier. For example:
Encode-SqlName "Table:Test" returns the string "Table%3ATest".
Decode-SqlName "Table%3ATest" returns "Table:Test".
For more information, see Using SQL Server Identifiers in PowerShell.
Converting URNs to Paths
The SQL Server Management Object model (SMO) builds Uniform Resource Names (URN) for its objects. Each URN has the same information as a path to the object, but in a different form. For example, this is the path to a table:
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2008R2\Tables\Person.Address
And this is the URN to the same object:
Server[@Name='MyComputer']\Database[@Name='AdventureWorks2008R2']\Table[@Name='Address' and @Schema='Person']
The Convert-UrnToPath cmdlet converts SMO URN strings to Windows PowerShell paths. If node names contain extended characters that are not supported in Windows PowerShell path names, Convert-UrnToPath encodes them in their hexadecimal representation. For example "My:Table" is returned as "My%3ATable".
In Windows PowerShell, run Get-Help Convert-UrnToPath -Examples for examples of using the cmdlet.
Partial Parameter Names
You do not have to specify the entire name of a cmdlet parameter. You only have to specify enough of the name to uniquely separate it from the other parameters that are supported by the cmdlet. For example, these examples show three ways of specifying the Invoke-Sqlcmd -QueryTimeout parameter:
Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3
Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTime 3
Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryT 3
See Also