Início / dba / Perguntas / 195923
Accepted
McNets
Asked: 2018-01-23 06:14:48 +0800 CST

Existe alguma função interna (oculta) no MS-SQL para retirar as aspas de nomes de objetos?

  • 772

Às vezes, armazeno nomes de objetos (identificadores) em alguns de nossos bancos de dados, por exemplo, em algumas tabelas de parâmetros. Como seleciono registros dessas tabelas usando os operadores de comparação '=' ou 'LIKE', devo ter o cuidado de armazenar esses nomes sempre com ou sem colchetes .

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

ou

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

No entanto, o MS-SQL tem algumas funções nas quais você pode usar nomes de objetos com ou sem colchetes, por exemplo, a função OBJECT_ID(). Eu configurei um exemplo mínimo em 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

Agora posso usar OBJECT_ID() para verificar se a tabela TEST existe desta forma:

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. |

Não importa se eu passo o identificador TEST com ou sem colchetes, o analisador é inteligente o suficiente para remover os colchetes.

Bem, posso simular isso adicionando uma função escalar que remove colchetes de uma 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

E então use-o desta forma:

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

Mas minha pergunta é:

  • Existe alguma função interna oculta que remove colchetes usando T-SQL?

dbfiddle aqui

sql-server t-sql

4 respostas

  1. Às vezes, armazeno nomes de objetos em alguns de nossos bancos de dados

    Devo ter o cuidado de armazenar esses nomes sempre com ou sem colchetes.

    Um "nome de objeto" é tecnicamente chamado de identificador . Em alguns contextos aparecerá um identificador de código TSQL entre [ e ] ou " e ". Esses caracteres não fazem parte do identificador e você nunca deve armazená-los.

    Em vez disso, armazene o identificador como um nvarchar(128) (ou sysname) e adicione os delimitadores em tempo de execução usando a função QUOTENAME .

    O inverso de QUOTENAME é PARSENAME , que tem a capacidade adicional de navegar por nomes de várias partes.

    Observe que QUOTENAME tem um segundo parâmetro opcional e, se você especificar um caractere de aspas simples para esse parâmetro, QUOTENAME não criará uma expressão de identificador delimitado válida. Ele emite uma expressão literal varchar.

    • 18
  2. Best Answer

    Existe alguma função interna oculta que remove colchetes usando T-SQL?

    Não , não usando T-SQL.

    OBJECT_IDé uma função intrínseca . Ele é implementado diretamente no código executável do SQL Server, não no T-SQL; e não chama nenhum T-SQL quando invocado.

    Em tempo de execução, o id do objeto é obtido por meio do serviço de expressão chamando sqlmin!I4ObjIdWstr.

    A implementação então passa por todas as etapas necessárias para resolver os parâmetros de string fornecidos no id de um objeto no banco de dados referenciado.

    Uma das primeiras etapas inclui lidar com quaisquer identificadores delimitados na string via sqlmin!CbParseQuotesW. Em sentido estrito, essa é a função de código à qual você está se referindo, mas não pode ser acessada diretamente do T-SQL. Ele contém o seguinte código:

    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

    ...que são testes para lidar com os caracteres:

    • hex 22 = 34 de dezembro ="
    • hex 2E = dez 46 =.
    • hex 5B = dez 91 =[
    • hexadecimal 5D = dez 93 =]

    O resto do processo para resolver os parâmetros para um id envolve:

    • Iniciando uma transação automática somente leitura
    • Verificando os requisitos de banco de dados independente
    • Iterando por possíveis correspondências para o parâmetro name (usando a ordenação correta)
      • No nome do banco de dados fornecido (ou banco de dados de contexto atual)
      • No nome do esquema fornecido (ou sys , ou o esquema padrão do usuário etc.)
    • Obtendo os bloqueios de metadados necessários
    • Consultando o cache de metadados para uma correspondência
    • Buscando metadados no cache, se necessário
    • Verificando permissões (para acessar o ID do objeto)
    • Retornando o id do primeiro objeto correspondente (se houver)

    Em uma nota lateral, o código na pergunta:

    IF OBJECT_ID('TEST') IS NOT NULL

    ...não procura apenas tabelas. Para isso, seria necessário usar o segundo parâmetro de função. Além disso, ele só procura por qualquer objeto com escopo de esquema chamado TEST - portanto, uma exibição chamada BananaSchema.TEST corresponderia, por exemplo. Uma expressão melhor seria:

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

    Perguntas e respostas relacionadas:

    • 12

  3. O SQL Server obviamente tem algo interno que remove [square brackets](ou outros identificadores, como "double quotes").

    Quando você cria uma tabela como [dbo].[foo], você está certo, só foofica armazenado em sys.tablesand sys.objects, e não há como reclamar que o esquema [dbo](com os colchetes) não foi encontrado.

    Mas isso acontece dentro do código para CREATE TABLE. Eles podem estar usando PARSENAME(), como David apontou. Conectar o depurador pode indicar com certeza, mas isso importa?

    Você pode procurar em outro lugar para ver o que eles estão fazendo e, sys.sp_renamede fato, o rendimento que PARSENAME()é usado:

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

    Mas, novamente, não tenho certeza se entendi por que você deseja remover apenas às vezes os colchetes.

    Para mim, pessoalmente, uma porcentagem grande o suficiente do meu código é escrita para um público mais amplo, que usará o código em ambientes onde não tenho conhecimento ou controle sobre se eles estão usando identificadores não seguros. Então eu sempre tenho e sempre vou escrever (e preferir) código que use QUOTENAME()para gerar scripts que incluam qualquer tipo de identificador.

    Eu preferiria ter os colchetes lá o tempo todo, do que tirá-los e ser mordido na única vez em que foram necessários.

    • 7

  4. Quando colchetes são necessários -- é porque seu identificador já é uma palavra-chave reservada. Usá-los arbitrariamente não só não é necessário, como também leva a muita confusão, como você pode ver.

    Na minha opinião, a melhor maneira é evitar o uso de identificadores reservados em primeiro lugar.

    • -6

relate perguntas