SQL Server identifiers in PowerShell
The SQL Server provider for Windows PowerShell uses SQL Server identifiers in Windows PowerShell paths. SQL Server identifiers can contain characters that Windows PowerShell does not support in paths. You must escape these characters or use special encoding for them when using the identifiers in Windows PowerShell paths.
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.
SQL Server identifiers in Windows PowerShell paths
Windows PowerShell providers expose data hierarchies using a path structure similar to the Windows file system. The SQL Server provider implements paths to SQL Server objects. For the Database Engine, the drive is set to SQLSERVER:, the first folder is set to \SQL, and the database objects are referenced as containers and items. This is the path to the Vendor table in the Purchasing schema of the AdventureWorks2022 database in a default instance of the Database Engine:
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2022\Tables\Purchasing.Vendor
SQL Server identifiers are the names of SQL Server objects, such as table or column names. There are two types of SQL Server identifiers:
Regular identifiers are limited to a set of characters that are also supported in Windows PowerShell paths. These names can be used in Windows PowerShell paths without being changed.
Delimited identifiers can use characters not supported in Windows PowerShell path names. Delimited identifiers are called bracketed identifiers if they are enclosed in brackets ([IdentifierName]) and quoted identifiers if they are enclosed in double quotes ("IdentifierName"). If a delimited identifier uses characters not supported in Windows PowerShell paths, the characters must either be encoded or escaped before using the identifier as a container or item name. Encoding works for all characters. Some characters, such as the colon character (:), cannot be escaped.
SQL Server identifiers in cmdlets
Some SQL Server cmdlets have a parameter that takes an identifier as input. The parameter values are typically supplied as quoted string constants or in string variables. When identifiers are supplied as string constants or in variables, there is no conflict with the set of characters that are supported by Windows PowerShell.
SQL Server identifier tasks
| Task Description | Article |
|---|---|
| Describes how to specify an instance name, including the name of the computer the instance is running on. | Specify Instances in the SQL Server PowerShell Provider |
| Describes how to specify the hexadecimal encoding for characters in delimited identifiers that are not supported in Windows PowerShell paths. Also describes how to decode the hexadecimal characters. | Encode and Decode SQL Server Identifiers |
| Describes how to use the Windows PowerShell escape character for characters not supported in PowerShell paths. | Escape SQL Server Identifiers |