2

When querying metadata for a specific table, I can either

  1. use the OBJECT_ID function or
  2. join the relevant system tables (sys.tables and sys.schemas).

For example, both of those queries will return all DML triggers for table myTable:

SELECT *
  FROM sys.triggers AS t
 WHERE t.parent_id = OBJECT_ID('[dbo].[myTable]', 'U')

SELECT *
  FROM sys.triggers AS tr
       INNER JOIN sys.tables AS tab ON tr.parent_id = tab.object_id 
       INNER JOIN sys.schemas AS s ON tab.schema_id = s.schema_id 
 WHERE s.name = 'dbo' AND tab.name = 'myTable';

Is it purely a matter of taste, or is there a good reason to prefer one over the other (if both will solve your problem)?

My preliminary analysis is that:

  • Option 1 is shorter (obviously).
  • With Option 2 I don't need to worry about quoting/escaping the table/schema name (if it contains special characters).
  • The query plans are different (but unlikely to make a huge difference, since this is not an operation I plan to do frequently).

Did I miss anything important?

0
4

Converted from comments:

The only thing you should really be aware of with OBJECT_ID is that it's database-context sensitive, and you may also need to supply that if you're querying across databases, eg from master to any_user_database. Erik Darling

I remember an old Connect item (oh, found number 432497) complaining about OBJECT_ID. If I recall correctly, OBJECT_ID does not always respect transaction isolation level (also OBJECT_NAME, SCHEMA_NAME, etc.). Querying sys were safe. It can make sense in DDL-triggers. i-one

yeah, the metadata functions will block even if read uncommitted is specified rather than read another transaction's dirty data - Martin Smith

Related, related. - i-one

I talk about the isolation level issue here: Bad habits: Using (certain) metadata "helper" functions. - Aaron Bertrand

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.