Feature request: add braces around objects and column names

[sonnemaf]

Hi,

I would like an option to get square braces add around objects and column names

before

SELECT ProductName 
FROM dbo.Products

after

SELECT [ProductName]
FROM [dbo].[Products]

Not sure if this achievable.

[ErikEJ]

@sonnemaf I assume you mean for the formatting feature?

[sonnemaf]

Yes, that is wat I meant.

[daiplusplus]

+1 for the request, but with more options:

• An option to use ISO SQL Double-Quotes (aka "ANSI Quotes" or QUOTED_IDENTIFIER) instead of Square-Brackets for escaping names, e.g. SELECT foo."bar" AS "baz" FROM foo;.
  • I'd also like to suggest optional support for backticks too, to make it easier to port-and-format queries between T-SQL and MySQL/MariaDb - or make it possible for an extension to SqlScriptDom to support it, for example.,
• An option to only escape names when absolutely necessary (e.g. when a name contains spaces or otherwise invalid chars; and when a name matches an existing T-SQL keyword which would make the statement invalid).
  • I'd also like to propose another option that broadens the set of keywords to include contextual-keywords that might not apply in the current context but which SSMS always highlights anyway, such as "name" and "description" - even when they're valid column names. This also benefits workflows where the SQL passes through less-sophisticated parsers/formatters. I've included examples below.

---

In .editorconfig format, I propose something like below (and feel free to do bikeshedding on the options and names, I'm not wedded to them)

[*.sql]

quoted_identifier_style = iso-quotes | square-brackets | backticks
quoted_identifier_for_names = always | keywords | minimal
quoted_identifier_for_types = always | minimal

• The quoted_identifier_style option is self-explanatory.
• The quoted_identifier_for_names option applies to all user-supplied identifiers used as schema-names, object-names and column-names, or references-to thereof; but not references-to-types in in a column-definition or DECLARE statement.
• The quoted_identifier_for_types option only applies to type-references used in a declaration, such as a column's type in a CREATE TABLE declaration, or a DECLARE statement.
  • This option is because SSMS will escape column type-names when you use the "Script table as > CREATE..." menu option; because right now SSMS always escapes everything: e.g. [MyColumnName] [nvarchar](255) NOT NULL instead of MyColumnName nvarchar(255) NOT NULL.

As for the possible options:

• In all cases, user-supplied identifiers and names that do not match Regular Identifiers (i.e. ^[A-Za-z_@#][\w_$]*$), such as those with spaces, brackets, quotes, and any symbols/punctuation/emoji will always be escaped.
• The always option will always escape all user-supplied identifiers (except when used as a type-reference, described above), even when completely unnecessary.
  • This option is for the current SSMS "Script table as... > CREATE..." behaviour, going back decades to MSSQL 7.0's tools AFAICR.
• The keywords option will escape user-supplied identifiers that match any T-SQL keyword, in addition to those not matching Regular Identifiers.
  • Specifically: the set of keywords depends on the current T-SQL language version and database edition, but will include both context-free and context-sensitive keywords, and also all other reserved-words that strictly-speaking do not need to be escaped but which SSMS currently always-highlights, like name, description, and GO.
  • This may-or-may-not include other keywords, like ODBC keywords, ISO SQL keywords and other reserved-words that MSSQL's docs mention.
  • The keywords option does not apply to quoted_identifier_for_types because all built-in type-names are T-SQL keywords already, like int.
• The minimal option will only escape a user-supplied identifier if it absolutely needs to, such as when it matches a context-free T-SQL keyword (like CREATE or TABLE) or if it does not match Regular Identifiers.
  • So it would not escape a column named description, even though SSMS highlights "description" as a keyword.
  • When using quoted_identifier_for_types = minimal, then T-SQL keywords for built-in types (like int and varchar) are not escaped; whereas when referencing a user-defined-type which does match a keyword would be escaped (e.g. DECLARE @var dbo."Trigger" - where dbo."Trigger" was created using CREATE TYPE dbo."Trigger" ...).

For example:

Note that for readability and clarity I've manually-aligned the column-definitions in these examples; whereas in reality that would be controlled by entirely separate .editorconfig options defined elsewhere.

always (with square-brackets):

This is the current behaviour of SSMS 22.x, but also in all previous versions too, going back as far as I can remember to SQL Server 7.0's Enterprise Manager and Query Analyzer tools:

-- Using these .editorconfig rules:
-- quoted_identifier_style = square-brackets
-- quoted_identifier_for_names = always
-- quoted_identifier_for_types = always

DECLARE @varName [int] = 3;

CREATE TABLE [dbo].[People] (

    [SafeName]           [int]                         NULL,
    [Name]               [nvarchar](255)           NOT NULL,
    [Table]              [varchar](255)                NULL,
    [Column]             [dbo].[MyUserDefinedType] NOT NULL,
    [Column with spaces] [dbo].[MyUdt with spaces] NOT NULL
);

always (with iso-quotes):

-- Using these .editorconfig rules:
-- quoted_identifier_style = iso-quotes
-- quoted_identifier_for_names = always
-- quoted_identifier_for_types = always

DECLARE @varName "int" = 3;

CREATE TABLE "dbo"."People" (

    "SafeName"           "int"                         NULL,
    "Name"               "nvarchar" (255)          NOT NULL,
    "Table"              "nvarchar" (255)              NULL,
    "Column"             "dbo"."MyUserDefinedType" NOT NULL,
    "Column with spaces" "dbo"."MyUdt with spaces" NOT NULL
);

keywords (with iso-quotes): All identifiers are escaped, except built-in type-names,(this is how SSMS 21 and SSDT in VS2022 currently behaves in some - but not all - situations):

-- Using these .editorconfig rules:
-- quoted_identifier_style = iso-quotes
-- quoted_identifier_for_names = keywords 
-- quoted_identifier_for_types = minimal

DECLARE @varName int = 3;

CREATE TABLE "dbo"."People" (

    SafeName             int                         NULL,
    "Name"               nvarchar(255)           NOT NULL,
    "Table"              varchar(255)                NULL,
    "Column"             dbo.MyUserDefinedType   NOT NULL,
    "Column with spaces" dbo."MyUdt with spaces" NOT NULL
);

Observe that "Name" was escaped, despite being a valid column name that, strictly speaking, does not need to be escaped.

always-except-types (with iso-quotes): All identifiers are escaped, except built-in type-names,(this is how SSMS 21 and SSDT in VS2022 currently behaves in some - but not all - situations):

-- Using these .editorconfig rules:
-- quoted_identifier_style = iso-quotes
-- quoted_identifier_for_names = minimal
-- quoted_identifier_for_types = minimal

DECLARE @varName int = 3;

CREATE TABLE "dbo"."People" (

    SafeName             int                         NULL,
    Name                 nvarchar(255)           NOT NULL,
    "Table"              varchar(255)                NULL,
    "Column"             dbo.MyUserDefinedType   NOT NULL,
    "Column with spaces" dbo."MyUdt with spaces" NOT NULL
);

Observe that "Name" was not escaped here.

---

As an offtopic remark, I note that having SqlScriptDom support these iso-quotes, keywords, and minimal options means the built-in QUOTENAME(name, quote) function should be extended with a third-parameter (corresponding to the quoted_identifier_for_names rule here) to skip the quote-name process if the name parameter argument does not match any T-SQL keyword. The STRING_ESCAPE function should also be looked at for possible revision too.