Database Programming: OBJECT_NAME Takes Two Parameters

  • Article

This post is a cousin of sorts to last December’s post regarding the OBJECT_ID function. I learned this from Dirk Gubbels when he sent me a follow-up email to the seasonal T-SQL he passed along (the posting of which spawned a small flood). This “trick” is actually a SQL Server 2005 enhancement, so it will also work in SQL Server 2008, but unfortunately not in SQL Server 2000.

Here’s a small T-SQL script which makes the point succinctly:

set nocount on
use msdb
go
-- note the feature we "discovered" in the previous post
select object_id('master.dbo.sysdatabases')
-- use the functionality of the previous discovery to build
-- an environment-neutral demonstration of the new "discovery"
select object_name(object_id('master.dbo.sysdatabases'),db_id('master'))
-- this instantiation of the new discovery should produce identical
-- results to the above
select object_name(-202,6)
go

This script will produces the following output on the SQL Server 2005 instance on my laptop:

-----------
-202

-------------------
sysdatabases

-------------------
sysdatabases

In SQL Server 2000, if you wanted to run the OBJECT_NAME() function outside the current database context, dynamic SQL was the only option (to either build a call to OBJECT_NAME() or the correct copy of the sysobjects table). This new syntax allows the call to be built in-line, which offers an additional arrow in the quiver of T-SQL coders on the SQL Server 2005 and SQL Server 2008 platforms.

Thanks for the head-up, Dirk!

-wp