Edit

SQL Server Identifiers in PowerShell

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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, see 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

See Also