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

sys.objects
sys.objects
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.

Continue reading at SQLStudies.com

Continue reading on SQLStudies.com.

54321
(1 vote. Average 5 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *