sys.objects VS helper functions (OBJECT_ID, OBJECT_NAME, etc)

1

October 11, 2017 by Kenneth Fisher

I’ve you’ve done much work with the system views (DMVs for example) then you’ve had to translate an object_id into a schemaname and objectname and vise versa. To do that you’ve either used the functions OBJECT_ID, OBJECT_SCHEMA_NAME, and OBJECT_NAME or sys.objects and sys.schemas.

So what’s the difference? Well, each version has its pros and cons.


Functions (OBJECT_NAME, OBJECT_ID, etc)

Pros

  • Quick and easy. Very few characters, no join required.
  • Usually (all I’ve looked at) can either be passed the database_id as a seperate parameter or the database name as part of a fully qualified object name. This means if you are querying sys.dm_exec_procedure_stats (for example) you can collect data across every database at once.

 
Cons

 


Views (sys.objects, sys.schemas, etc)

Pros

  • Follows the transaction isolation level of the session and/or you can change the isolation level for the specific view. So if you are using NOLOCK then the whole query will actually use NOLOCK.
  • There is a lot of information available beyond just the name.

 
Cons

  • The code is longer/requires more typing.
  • These are specific to a single database. Unless you create a master view you are going to have a problem if your query hits data across multiple databases.

 


So which should you use? Well, like most things in the database world, it depends. Do you need to cross databases? Do you need to use NOLOCK for a diagnostic query? Do you need additional information available in one of the views that isn’t available in one of the helper functions?

In the end, both are useful, so learn both. Learn how to use them, learn the pros and the cons, and don’t get so stuck on one or the other that you forget to use the one that’s most helpful in your situation.

One thought on “sys.objects VS helper functions (OBJECT_ID, OBJECT_NAME, etc)

  1. […] Kenneth Fisher compares and contrasts querying system tables versus using built-in helper functions …: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,151 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: