Skip to content

Latest commit

 

History

History
60 lines (44 loc) · 4.67 KB

sql-server-identifiers-in-powershell.md

File metadata and controls

60 lines (44 loc) · 4.67 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords
SQL Server Identifiers in PowerShell
Learn about the paths that Windows PowerShell providers use to expose data hierarchies, and about the need to encode certain characters not supported by PowerShell in these paths.
markingmyname
maghan
matteot, drskwier
03/14/2017
sql
sql-server-powershell
conceptual
Cmdlets [SQL Server], Encode-Sqlname
PowerShell [SQL Server], identifiers
Encode-Sqlname cmdlet
PowerShell [SQL Server], Encode-Sqlname
Decode-Sqlname cmdlet
PowerShell [SQL Server], Decode-Sqlname
identifiers [SQL Server], PowerShell
Cmdlets [SQL Server], Decode-Sqlname

SQL Server Identifiers in PowerShell

[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW]

The [!INCLUDEssNoVersion] provider for Windows PowerShell uses [!INCLUDEssNoVersion] identifiers in Windows PowerShell paths. [!INCLUDEssNoVersion] 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.

[!INCLUDE sql-server-powershell-version]

SQL Server Identifiers in Windows PowerShell Paths

Windows PowerShell providers expose data hierarchies using a path structure similar to the Windows file system. The [!INCLUDEssNoVersion] provider implements paths to [!INCLUDEssNoVersion] objects. For the [!INCLUDEssDE], 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 [!INCLUDEssSampleDBobject] database in a default instance of the [!INCLUDEssDE]:

SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2022\Tables\Purchasing.Vendor

[!INCLUDEssNoVersion] identifiers are the names of [!INCLUDEssNoVersion] objects, such as table or column names. There are two types of [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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