THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Metadata Power

I love the new catalog views and DMVs in SQL Server 2005, and I have written quite a few of my own views on top of them, to give me more specific information that I need. However, many of the views contain object_id information, and I would much rather see object names.

Yes, I know there is an object_name function that returns a name, given an object id. But you have to be IN the database which contains the object in order to get valid information from the function. Wouldn't it be nice if object_name took a second parameter of the database id? For example, the DMV sys.dm_tran_locks contains object_id and database_id. If I want to return a list of all the objects with locks, I'd like to be able to apply a function in every row and get the information I want.

Sybase enhanced their object_name function over 15 years ago, and I have been asking the MS engineers to do the same for almost that long. But nothing happened. Until I decided to try posting my request on the SQL Server Connect site, which you should take a look at if you haven't already:

On September 13, the engineers at Microsoft responded that the object_name function would be enhanced per my request, in SQL Server 2005, SP2. (Of course, they didn't say when that would be available.)  They also said they would introduce a new function called object_schema_name which returns the schema name of the object. (Presumably, it takes an object_id as an input parameter.)

You can see my request and the response from Microsoft here:

So I know what I'll be doing as soon as I get my hands on the next SP!

-- Kalen

Published Sunday, September 24, 2006 9:01 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Louis Davidson said:

This is not a scoop or anything, but it is quite newsworthy.  It is also a good way to kick off...
September 25, 2006 7:35 PM

Thomas Pullen said:

Well done for requesting (& getting!) this. It will be so useful. And surely SP2 won't be too far off.
September 26, 2006 2:33 AM

Mike Good said:

I have also missed this from old Sybase days.  Thanks for giving this the required visibility!  I have sp2 installed and the feature is now there!  

The object_name() built-in function now has a 2nd completely optional parameter.  On my server, the DB id of msdb is 4.  

use msdb

select object_name(2146106686), object_name(2146106686, 4)

use master

select object_name(2146106686), object_name(2146106686, 4)

/* yields the following:

restorefile   restorefile

NULL          restorefile


March 26, 2007 2:46 PM

Louis Davidson said:

This is not a scoop or anything, but it is quite newsworthy. It is also a good way to kick off my sqlblog

April 6, 2007 8:42 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement