Início / dba / Perguntas / 27950
Accepted
Iain Samuel McLean Elder
Asked: 2012-11-01 05:48:40 +0800 CST

Por que OBJECTPROPERTY retorna NULL?

  • 772

Digamos que você queira consultar um banco de dados para descobrir os tipos de gatilho que ele contém. Uma maneira de fazer isso é usar a função OBJECTPROPERTY em todos os objetos acionadores do banco de dados.

Às vezes, a função OBJECTPROPERTY produz um resultado confuso. Sua saída parece depender do contexto do banco de dados.

A consulta de exemplo a seguir retorna uma linha para cada um dos gatilhos sysmail no msdb:

SELECT
  object_id,
  name,
  OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS IsInsertTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS IsUpdateTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS IsDeleteTrigger
FROM msdb.sys.objects
WHERE
  [type] = 'TR' AND
  name LIKE 'trig_sysmail_%';
GO

A intenção é descobrir qual ação DML disparará cada gatilho. Por exemplo, a IsInsertTriggercoluna contém 1 se o acionador for definido como AFTER INSERT, e 0 caso contrário.

Quando executo a consulta no contexto do msdb, o conjunto de resultados contém 0 ou 1 em cada uma das colunas computadas. Se parece com isso:

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         0               1               0
745105745   trig_sysmail_account         0               1               0
761105802   trig_sysmail_profileaccount  0               1               0
777105859   trig_sysmail_profile_delete  0               0               1
793105916   trig_sysmail_servertype      0               1               0
809105973   trig_sysmail_server          0               1               0
825106030   trig_sysmail_configuration   0               1               0
841106087   trig_sysmail_mailitems       0               1               0
857106144   trig_sysmail_attachments     0               1               0
873106201   trig_sysmail_log             0               1               0

Quando executo a consulta no contexto do mestre, o conjunto de resultados contém NULL em cada uma das colunas computadas. Se parece com isso:

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         NULL            NULL            NULL
745105745   trig_sysmail_account         NULL            NULL            NULL
761105802   trig_sysmail_profileaccount  NULL            NULL            NULL
777105859   trig_sysmail_profile_delete  NULL            NULL            NULL
793105916   trig_sysmail_servertype      NULL            NULL            NULL
809105973   trig_sysmail_server          NULL            NULL            NULL
825106030   trig_sysmail_configuration   NULL            NULL            NULL
841106087   trig_sysmail_mailitems       NULL            NULL            NULL
857106144   trig_sysmail_attachments     NULL            NULL            NULL
873106201   trig_sysmail_log             NULL            NULL            NULL

O MSDN observa que a função OBJECTPROPERTY retorna NULL quando:

  1. o nome da propriedade não é válido.
  2. o ID do objeto não é válido.
  3. id é um tipo de objeto sem suporte para a propriedade especificada.
  4. o chamador não tem permissão para visualizar os metadados do objeto.

Posso descartar os motivos 1 e 3 porque a consulta retorna o resultado correto no contexto do msdb.

A princípio, pensei que poderia ser um problema de permissões entre bancos de dados (motivo 4), mas sou administrador do sistema no servidor.

Isso deixa o motivo 2, que me deixa com estas perguntas:

O ID do objeto é inválido em uma consulta entre bancos de dados?

A função OBJECTPROPERTY de qual banco de dados está sendo chamada?

sql-server sql-server-2008

2 respostas

  1. Best Answer

    OBJECTPROPERTY é local para o banco de dados em que a consulta é executada. Portanto, o object_idpassado é resolvido contra master.sys.objects: mas o object_id vem demsdb

    Então aqui você tem o caso 2.

    No meu servidor, tenho 37 valores de object_id correspondentes entre msdbe SomeDBOnMyServer. Mas os nomes são diferentes.

    USE SomeDBOnMyServer
GO
SELECT
  object_id,
  name,
  OBJECT_NAME(object_id)
FROM msdb.sys.objects
WHERE OBJECT_NAME(object_id) <> name

    Obviamente, tenho muitas linhas onde OBJECT_NAME(object_id)é NULL que são filtradas aqui

    • 6

  2. Como gbn observou, você precisa executar esta consulta no contexto do banco de dados msdb OBJECTPROPERTYpara resolver corretamente. Se você deseja tornar seu banco de dados de script agnóstico, pode escrever uma consulta mais complicada para obter as mesmas informações:

    SELECT 
  t.[object_id], t.name, 
  IsInsertTrigger = MAX(CASE WHEN i.[type] IS NULL THEN 0 ELSE 1 END),
  IsUpdateTrigger = MAX(CASE WHEN u.[type] IS NULL THEN 0 ELSE 1 END),
  IsDeleteTrigger = MAX(CASE WHEN d.[type] IS NULL THEN 0 ELSE 1 END)
FROM msdb.sys.triggers AS t
LEFT OUTER JOIN 
  msdb.sys.trigger_events AS i
  ON t.[object_id] = i.[object_id] AND i.[type] = 1
LEFT OUTER JOIN 
  msdb.sys.trigger_events AS u
  ON t.[object_id] = u.[object_id] AND u.[type] = 2
LEFT OUTER JOIN 
  msdb.sys.trigger_events AS d
  ON t.[object_id] = d.[object_id] AND d.[type] = 3
WHERE t.name LIKE 'trig[_]sysmail[_]%'
GROUP BY t.[object_id], t.name;
    • 6

relate perguntas