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

• Iain Samuel McLean Elder: +1 for explaining that the object id is from one database and is being looked up in another database. I think that query returns no rows in my database; there happen to be no object ids in common.

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;

• Iain Samuel McLean Elder: +1 for the query! I didn't know about sys.trigger_events. The documentation for sys.triggers makes no mention of it, but it's clearly related.
• Iain Samuel McLean Elder: And thanks for fixing the subtle bug in my LIKE caluse.
• Aaron Bertrand: @isme you'd have to have done some pretty gnarly stuff in msdb to get caught out by it, but it's a habit, sorry. :-)