Why does OBJECTPROPERTY return NULL?

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 t

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

StackExchange Database Administrators Q#27950, answer score: 6