Sql-server – ny (hidden) built-in function on MS-SQL to unquote object names

sql servert-sql

Sometimes I store object names (identifiers) in some of our databases, for example in some parameter tables. Because I select records from these tables using the '=' or 'LIKE' comparison operators, I must take care to store these names always with or without brackets.

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = '[TABLE_NAME]';

or

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = 'TABLE_NAME';

However MS-SQL has some functions where you can use object names with or without brackets, for example the OBJECT_ID() function. I've set up a minimal example on dbfiddle.uk.

CREATE TABLE TEST
(
    ID     INT IDENTITY(1,1) PRIMARY KEY,
    OBJECT sysname NOT NULL
);
GO

INSERT INTO TEST VALUES ('[obj1]'),('obj2'),('obj3'),('[obj4]');
GO

Now I can use OBJECT_ID() to check if the table TEST exists in this way:

IF OBJECT_ID('TEST') IS NOT NULL
BEGIN
    SELECT 'TEST EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID    |
| :----------- |
| TEST EXISTS. |

IF OBJECT_ID('[TEST]') IS NOT NULL
BEGIN
    SELECT '[TEST] EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID      |
| :------------- |
| [TEST] EXISTS. |

It doesn't matter if I pass the identifier TEST with or without brackets, parser is smart enough to remove the brackets.

Well, I can simulate this by adding a scalar function that remove brackets from one string: 

CREATE FUNCTION UNQUOTENAME(@TXT NVARCHAR(MAX)) 
RETURNS NVARCHAR(MAX)
AS
    BEGIN
        RETURN IIF(LEFT(@TXT, 1) = N'[' AND RIGHT(@TXT, 1) = N']', 
                   SUBSTRING(@TXT, 2, LEN(@TXT) -  2), 
                   @TXT);
    END;
GO

And then use it in this way:

SELECT dbo.UNQUOTENAME (N'[FIELD]') NAME1, N'FIELD' NAME2;
GO

NAME1 | NAME2
:---- | :----
FIELD | FIELD

SELECT ID, OBJECT 
FROM   TEST 
WHERE OBJECT LIKE 'obj%';
GO

ID | OBJECT
-: | :-----
 2 | obj2  
 3 | obj3  

SELECT ID, dbo.UNQUOTENAME(OBJECT) 
FROM   TEST 
WHERE  dbo.UNQUOTENAME(OBJECT) LIKE 'obj%';
GO

ID | (No column name)
-: | :---------------
 1 | obj1
 2 | obj2
 3 | obj3
 4 | obj4

But my question is:

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

dbfiddle here

Best Answer

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:

Related Question