Why does OBJECTPROPERTY return NULL?

Question

Say you want to query a database to discover the types of trigger it contains. One way to do this is to use the OBJECTPROPERTY function on all trigger objects in the database.

Sometimes the OBJECTPROPERTY function produces a confusing result. Its output seems to depend on the database context.

The following example query returns a row for each of the sysmail triggers in 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

The intent is to find out what DML action will fire each trigger. For example, the IsInsertTrigger column contains a 1 if the trigger is defined as AFTER INSERT, and 0 otherwise.

When I execute the query in the context of msdb, the result set contains a 0 or a 1 in each of the computed columns. It looks like this:

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

When I execute the query in the context of master, the result set contains NULL in each of the computed columns. It looks like this:

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 notes that the OBJECTPROPERTY function returns NULL when:

1. the property name is not valid.
2. the object id is not valid.
3. id is an unsupported object type for the specified property.
4. the caller does not have permission to view the object's metadata.

I can rule out reasons 1 and 3 because the query returns the correct result in the context of msdb.

At first I thought it might be a cross-database permissions issue (reason 4), but I am sysadmin on the server.

That leaves reason 2, which leaves me with these questions:

Is the object id invalid in a cross-database query?

Which database's OBJECTPROPERTY function is being called?

Answer 1

OBJECTPROPERTY is local to the database the query is run it. So the object_id passed in is resolved against master.sys.objects: but the object_id comes from msdb

So here you have case 2.

On my server, I have 37 matching object_id values between msdb and SomeDBOnMyServer. But the names are different.

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

Obviously, I have a lot of rows where OBJECT_NAME(object_id) is NULL that are filtered here

Answer 2

As gbn noted, you need to run this query in the context of the msdb database in order for OBJECTPROPERTY to resolve correctly. If you want to make your script database agnostic then you can write a more convoluted query to get the same information:

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;