主页 / dba / 问题 / 27950
Accepted
Iain Samuel McLean Elder
Asked: 2012-11-01 05:48:40 +0800 CST

为什么 OBJECTPROPERTY 返回 NULL？

  • 772

假设您要查询数据库以发现其中包含的触发器类型。一种方法是对数据库中的所有触发器对象使用 OBJECTPROPERTY 函数。

有时 OBJECTPROPERTY 函数会产生令人困惑的结果。它的输出似乎取决于数据库上下文。

以下示例查询为 msdb 中的每个 sysmail 触发器返回一行：

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

目的是找出哪个 DML 操作将触发每个触发器。例如，IsInsertTrigger如果触发器定义为 ，则该列包含 1 AFTER INSERT，否则为 0。

当我在 msdb 的上下文中执行查询时，结果集在每个计算列中都包含 0 或 1。它看起来像这样：

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

当我在 master 上下文中执行查询时，结果集在每个计算列中都包含 NULL。它看起来像这样：

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

MSDN指出 OBJECTPROPERTY 函数在以下情况下返回 NULL：

  1. 属性名称无效。
  2. 对象 ID 无效。
  3. id 是指定属性的不受支持的对象类型。
  4. 调用者无权查看对象的元数据。

我可以排除原因 1 和 3，因为查询在 msdb 的上下文中返回了正确的结果。

起初我认为这可能是跨数据库权限问题（原因 4），但我是服务器上的系统管理员。

这留下了原因 2，这给我留下了以下问题：

跨数据库查询中的对象 ID 是否无效？

正在调用哪个数据库的 OBJECTPROPERTY 函数？

sql-server sql-server-2008

2 个回答

  1. Best Answer

    OBJECTPROPERTY 是运行查询的数据库的本地对象。所以object_id传入的是针对master.sys.objects: 但是 object_id 来自msdb

    所以在这里你有案例2。

    在我的服务器上，我在和之间有 37 个匹配的object_id值。但名称不同。msdbSomeDBOnMyServer

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

    显然，我有很多行OBJECT_NAME(object_id)是在这里过滤的 NULL

    • 6

  2. 正如 gbn 所指出的，您需要在 msdb 数据库的上下文中运行此查询才能OBJECTPROPERTY正确解析。如果你想让你的脚本数据库不可知，那么你可以编写一个更复杂的查询来获取相同的信息：

    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

相关问题