Is there any (hidden) built-in function on MS-SQL to unquote object names?

Sometimes I store objects names in some of our databases

I must take care to store this names always with or without brackets.

An "object name" is technically called an identifier. In some contexts an identifier will be appear TSQL code surrounded by [ and ] or " and ". These characters are not part of the identifier, and you should never store them.

Instead store the identifier as a nvarchar(128) (or sysname), and add the delimiters at runtime using the QUOTENAME function.

The inverse of QUOTENAME is PARSENAME, which has the additional ability to navigate multi-part names.

Note that QUOTENAME has an optional second parameter, and if you specify a single quote character for that parameter QUOTENAME does not create a valid delimited identifier expression. It emits a varchar literal expression.


Is there any hidden built-in function that removes brackets using T-SQL?

No, not using T-SQL.

OBJECT_ID is an intrinsic function. It is implemented directly in the SQL Server executable code, not in T-SQL; and it does not call any T-SQL when invoked.

At runtime, the object id is obtained via the expression service calling sqlmin!I4ObjIdWstr.

The implementation then goes through all the necessary steps to resolve the provided string parameter(s) into the id of an object in the referenced database.

One of the first steps includes dealing with any delimited identifiers in the string via sqlmin!CbParseQuotesW. In a narrow sense, that is the code function you are referring to, but it is not accessible directly from T-SQL. It contains the following code:

cmp     r9d,22h
je      sqlmin!CbParseQuotesW+0x185
cmp     r9d,2Eh
je      sqlmin!CbParseQuotesW+0x139
cmp     r9d,5Bh
je      sqlmin!CbParseQuotesW+0xfe
cmp     r9d,5Dh
je      sqlmin!CbParseQuotesW+0xda

...which are tests to handle the characters:

  • hex 22 = dec 34 = "
  • hex 2E = dec 46 = .
  • hex 5B = dec 91 = [
  • hex 5D = dec 93 = ]

The rest of the process to resolve the parameters to an id involves:

  • Starting an automatic read-only transaction
  • Checking contained database requirements
  • Iterating through possible matches for the name parameter (using the correct collation)
    • In the supplied database name (or current context database)
    • In the supplied schema name (or sys, or the user's default schema etc.)
  • Taking the required metadata locks
  • Consulting the metadata cache for a match
  • Fetching metadata into cache if necessary
  • Checking permissions (to access the object id)
  • Returning the id of the first matched object (if any)

On a side note, the code in the question:

IF OBJECT_ID('TEST') IS NOT NULL

...does not look only for tables. To that would require using the second function parameter. In addition, it only looks for any schema-scoped object named TEST - so a view named BananaSchema.TEST would match, for example. A better expression would be:

IF OBJECT_ID(N'dbo.TEST', N'U') IS NOT NULL

Related Q & A:

  • What allows SQL Server to trade an object name for a string passed to a system procedure

SQL Server obviously has something internal that strips out [square brackets] (or other identifiers, like "double quotes").

When you create a table like [dbo].[foo], you're right, only foo gets stored in sys.tables and sys.objects, and there is no complaining that the schema [dbo] (with the square brackets) wasn't found.

But this happens inside the code for CREATE TABLE. They may be using PARSENAME(), as David pointed out. Hooking up the debugger could indicate for sure, but does it matter?

You can look elsewhere to see what they are doing, and sys.sp_rename in fact does yield that PARSENAME() is used:

select @UnqualOldName = parsename(@objname, 1),
        @QualName1 = parsename(@objname, 2),
        @QualName2 = parsename(@objname, 3),
        @QualName3 = parsename(@objname, 4)

But again, I'm not sure I understand why you want to only sometimes remove the square brackets.

For me, personally, a large enough percentage of my code is written for a broader audience, who will use the code in environments where I have no knowledge or control over whether they are using unsafe identifiers. So I always have and always will write (and prefer) code that uses QUOTENAME() to generate scripts that include any kind of identifier.

I would much rather have the square brackets there all the time, than take them away and get bitten the one time they were needed.

Tags:

Sql Server

T Sql

Recent Posts